views:

167

answers:

3

I just need to select the first day of the month of a given datetime variable.

I know it's quite easy to do using this kind of code :

select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) 
+ '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME)

but this is not very elegant, and probably not very fast either.

Is there a 'better way to do this ? (I'm using SQL Server 2008)

+14  A: 
SELECT DATEADD(month, DATEDIFF(month, 0, @mydate), 0) AS StartOfMonth
LukeH
Thanks luke. That's exactly what I was looking for !
Brann
+1  A: 

It is probably quite fast. Why not create it as a sql function.

CREATE FUNCTION [dbo].[GetFirstDayOfMonth] ( @InputDate    DATETIME )
RETURNS DATETIME
BEGIN

    RETURN CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + 
                CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

END
GO
dove
+3  A: 

The casting of a string (i.e. "5/1/2009") to datetime is certainly more legible but we found code a while back that would return the first of the month...

DECLARE @Date DATETIME
//...
SELECT DATEADD(mm, DATEDIFF(mm,0,@Date), 0)
Mayo