Returning specific records by month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Returning specific records by month

Author

Topic

paulferree
Starting Member

8 Posts

Posted - 2008-08-21 : 12:52:44

I think this is a stupid question...but if I have a datetime column in my table...how would I query to retrieve all records that have a January timestamp?

Thanks,
Paul

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 13:12:31

SELECT * FROM YourTable WHERE MONTH(datecolumn)='January'

paulferree
Starting Member

8 Posts

Posted - 2008-08-21 : 14:25:47

Well this is the format the dates are in "1/19/2007" so 'January' won't work.

I did figure out doing it this way...but I think their's probably something easier:

SELECT * FROM tblCalendar_Events
WHERE calevent_start_date >= '8/01/2008' AND calevent_start_date < '9/01/2008'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-21 : 18:34:01

I assume that you are really asking for January of a particular year.

If you are passing dates as strings in SQL Server, use the universal time format of YYYYMMDD.


select *
from
	tblCalendar_Events
where
	-- GE start of January
	calevent_start_date >= '20080101'	and
	-- Before February
	calevent_start_date <  '20080201'

More explanation here:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-21 : 18:35:47

quote:
Originally posted by visakh16

SELECT * FROM YourTable WHERE MONTH(datecolumn)='January'

I think your statement should actually be this, since the MONTH function returns an integer:

SELECT * FROM YourTable WHERE MONTH(datecolumn) = 1

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 23:38:02

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by visakh16

SELECT * FROM YourTable WHERE MONTH(datecolumn)='January'

I think your statement should actually be this, since the MONTH function returns an integer:
SELECT * FROM YourTable WHERE MONTH(datecolumn) = 1

CODO ERGO SUM

yup. thanks for spotting it out

mobile@digitaltrendz.co.z
Starting Member

25 Posts

Posted - 2008-08-23 : 13:30:39

Hi

Convert the date column and the where search criteria format.

WHERE CONVERT(varchar(50),[Date],6) = CONVERT(varchar(50),GETDATE(),6). Just need to look up the correct format. Google sql convert

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-25 : 03:07:10

quote:
Originally posted by mobile@digitaltrendz.co.z

Hi

Convert the date column and the where search criteria format.

WHERE CONVERT(varchar(50),[Date],6) = CONVERT(varchar(50),GETDATE(),6). Just need to look up the correct format. Google sql convert

Read the question again. That wont do the job that OP asked for

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources