SELECT month and year FROM datetime

[Call-911]

Hello All,

 

I have a datetime column. I'm making an archive, and I need to query the database to get all results from a specific month and year.

 

I can get all results from a year by doing:

 

SELECT *

FROM news

WHERE show = 'true' AND date LIKE '%2011%'

ORDER BY date desc, id2 desc

 

But if I try to add in the month

 

SELECT *

FROM news

WHERE show = 'true' AND date LIKE '%2011-01%'

ORDER BY date desc, id2 desc

 

it doesn't work.

 

Any thoughts, or any idea how to get results from the datetime using just the month and date?

[Brian Swan]

Give this a try:

 

SELECT *

FROM news

WHERE show = 'true' AND YEAR(date) = 2011 AND MONTH(date) = 1

ORDER BY date desc, id2 desc

 

 

Hope that helps.

 

-Brian

[Call-911]

You sir are a genius!  Perfect!!

[AndrewS78]

Give this a try:

 

SELECT *

FROM news

WHERE show = 'true' AND YEAR(date) = 2011 AND MONTH(date) = 1

ORDER BY date desc, id2 desc

 

 

Hope that helps.

 

-Brian

 

Can this be used to get the Hours and Minutes separately from the time ?? I mean something like "Hours(time) = 10"  :-\

[Muddy_Funster]

have a look at here:

http://msdn.microsoft.com/en-us/library/ms186724.aspx