Ad:

MySQL Discussion Thread
Unsolved
Views: 3133

Jul 7th, 2009

Extracting Month and day from datetime..

Expand Post »

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:

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.

Similar Threads

Datetime conversion in C#
DateTime field - How do I assign a time other than current? in VB.NET
how to get day, month and year in JSP
Current month name in ASP.NET
Getting first day of the month in ASP.NET

dopeynite

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jan 2009

Permalink

Jul 8th, 2009

Re: Extracting Month and day from datetime..

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.

Fest3er

Reputation Points: 51

Solved Threads: 35

Posting Whiz in Training

Offline

238 posts
since Aug 2007

Permalink

Jul 8th, 2009

Re: Extracting Month and day from datetime..

thank-you.... =D

dopeynite

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Jan 2009

Permalink

Aug 27th, 2009

Extracting year from date.

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.

gaurav252

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Aug 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Optimize SQL

Next Thread in MySQL Forum Timeline: Need Help with SQL.. ORA00933

DaniWeb Message

Cancel Changes

User Name
Password