Get Year + Month from Datetime

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get Year + Month from Datetime

Author

Topic

Will H
Yak Posting Veteran

56 Posts

Posted - 2007-11-30 : 12:01:46

Hi - Anyone have suggestions for converting a datetime to just the year and month, while setting day to 1 and time to zero (12am)??

I've been using something like this:

SELECT Convert(DateTime, Convert(varchar(4), YEAR(GetDate())) + '-' + Convert(Varchar(2), MONTH(GetDate())) + '-01')

-- Input: 2007-11-30 08:58:56.463
-- Output: 2007-11-01 00:00.000

This is the exact input/output I would like, but the problem is it ends up being a lot to type over and over. I don't have permissions to write a UDF on the database. I'm wondering if anyone has any ideas on getting this same functionality w/ less typing or better effeciency.

Thnx - will

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-30 : 12:42:02