MySQL

Extracting Month and day from datetime..

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2009

Posts: 3

Reputation: dopeynite is an unknown quantity at this point

Solved Threads: 0

dopeynite

Offline

Newbie Poster

Extracting Month and day from datetime..

Jul 7th, 2009

Hi.. I'm trying to extract the day, month, and year from a datetime to use it within a 'WHERE' statement.

Table: workshops
workshopid - int(10)
workshopgroupcoursecode - text
title - varchar(255)
description - text

Table: workshops
workshopscheduleid - int(10)
workshopid - int(10)
startdatetime - datetime
enddatetime - datetime
timedescription - text

Query:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT workshops.workshopid, EXTRACT(DAY FROM workshopschedules.startdatetime) as d, workshops.title,
FROM workshopschedules,  workshops
WHERE MONTH(workshopschedules.startdatetime) = '$month' AND YEAR(workshopschedules.startdatetime) = '$year'";
 
SELECT workshops.workshopid, EXTRACT(DAY FROM workshopschedules.startdatetime) as d, workshops.title,
FROM workshopschedules,  workshops
WHERE Month(workshopschedules.startdatetime) = '$month' AND Year(workshopschedules.startdatetime) = '$year'";

****You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM workshopschedules, workshops WHERE Month(workshopsched

does mysql not allow me to extract the day and month and use it in a WHERE condition??

Last edited by Ezzaral; Jul 7th, 2009 at 5:03 pm. Reason: Added [code] [/code] tags. Please use them to format any code that you post.

•

Join Date: Aug 2007

Posts: 165

Reputation: Fest3er is an unknown quantity at this point

Solved Threads: 18

Fest3er

Offline

Junior Poster

Re: Extracting Month and day from datetime..

Jul 8th, 2009

It's telling you exactly where to look for the error. Hint: look at the first non-whitespace character before 'FROM'.

Give up? OK. You have a trailing comma in your AS clause. (You've no idea how many times I've hit this in the past 7 years just from editting a script or a schema.) Otherwise, the syntax looks OK.

When the error says to look 'near', it usually means to look before. In time and with practice, your brain will learn to grok what your eye is seeing.

•

Join Date: Jan 2009

Posts: 3

Reputation: dopeynite is an unknown quantity at this point

Solved Threads: 0

dopeynite

Offline

Newbie Poster

Re: Extracting Month and day from datetime..

Jul 8th, 2009

thank-you.... =D

•

Join Date: Aug 2007

Posts: 5

Reputation: gaurav252 is an unknown quantity at this point

Solved Threads: 0

gaurav252

Offline

Newbie Poster

Extracting year from date.

Aug 27th, 2009

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT * FROM <tn> WHERE YEAR(date_mem)='yyyy';
select * from <tn> where year(date_mem)='yyyy';

Last edited by peter_budo; Aug 27th, 2009 at 11:23 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.