Help: filtering the month of a datetime SQL column

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi! Could anyone tell me what am I doing wrong with this code?
The goal is to filter the column SchedStart from the table Sched by month. SchedStart is in a DateTime format ("yyyy/MM/dd hh:mm:ss"). For example, select the rows in which SchedStart has a month that is = 11(November).
This code returns a null gridview:

SELECT SchedID FROM Sched WHERE (SchedStart = MONTH(SchedStart))

While this one gives me an error(below)

SELECT SchedID FROM Sched WHERE (SchedStart = (DATEPART(MM, SchedStart) = 11))

SQL Error:
SQL Execution Error.
Executed SQL statement: SELECT SchedID FROM Sched WHERE (SchedStart = (DATEPART(MM, SchedStart ) = 11))
Error Source: .Net Sql Client Data Provider
Error Message: Incorrect syntax near '='

Please tell me what am I doing wrong :( Thanks!

Posted 18-Nov-13 11:24am

Lin Jiamei

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

bowlturner · Accepted Answer · 2013-11-18T11:37:00

Solution 1

You don't seem to be using SchedStart correctly in either of them. I am assuming that you are meaning to use the column name in all 4 places.

in your first example you are saying where '2013-11-18' = 11; that is why you get null.

what you'd want is where v_Month = MONTH(SchedStart). If v_Month is 11 then you will get all the Novembers (no matter what year either)

Similar problem with the second one. but you've added in SQL syntax errors.

Posted 18-Nov-13 11:37am

bowlturner

Comments

Lin Jiamei 18-Nov-13 17:47pm

What do you mean by using the column name in all 4 places?
With v_Month should the command be "SELECT SchedID FROM Sched WHERE (SchedStart =(11= MONTH(SchedStart)))"?
It gave me an error again with, the syntax errors.

Lin Jiamei 18-Nov-13 18:03pm

OH! I can not believe I did not get you, the first time! I got it now! You were a big help! Thank you bowlturner!!!!
SELECT SchedID FROM Sched WHERE 11= MONTH(SchedStart)

bowlturner 19-Nov-13 9:12am

Glad I could be of help!

Murugan Kolanji · Accepted Answer · 2013-11-18T12:15:00

Answer for the first one:

As per your writings, SchedStart is the datetime format, so your condition is wrong thats why its return the null value,
Your Query is:
SELECT SchedID FROM Sched WHERE (SchedStart = MONTH(SchedStart))

for example

SELECT SchedID FROM Sched WHERE ('10/01/2013 12:23:23' = 11)

For the second one:

Your Query is:

SELECT SchedID FROM Sched WHERE (SchedStart = (DATEPART(MM, SchedStart) = 11))

It should be:
SELECT SchedID FROM Sched WHERE (DATEPART(mm, SchedStart) = 11)

If you want the SchedID and SchedStart month to display, this is the correct query

Select SchedID, DatePart(mm,SchedStart) from Sched