How to Determine the First and the Last Day in a Year?
DECLARE @dt DATETIME
SET @dt = GETDATE()
SELECT CAST( CAST( YEAR(@dt) AS CHAR(4))+’0101′ AS DATETIME) AS StartOfYear , CAST( CAST( YEAR(@dt) AS CHAR(4))+’1231′ AS DATETIME) AS EndOfYear
StartOfYear EndOfYear
—————————————————— ——————————————————
2005-01-01 00:00:00.000 2005-12-31 00:00:00.000
(1 row(s) affected)
This is possibly one of the few occasions where I would prefer such string concatenation over date arithmetic. Why? Because a solution using SQL Server’s build-in datetime functions might look something like this:
DECLARE @dt DATETIME
SET @dt = ‘20050325’
SELECT DATEADD(m,1-MONTH(DATEADD(DAY,1-DAY(@dt),@dt)),DATEADD(DAY,1-DAY(@dt),@dt)) , DATEADD(yyyy,1,DATEADD(m,1-MONTH(DATEADD(DAY,1-DAY(@dt),@dt)),DATEADD(DAY,1-DAY(@dt),@dt)))-1
—————————————————— ——————————————————
2005-01-01 00:00:00.000 2005-12-31 00:00:00.000
(1 row(s) affected)
This is not really easy to read. However, to explain it a bit, this solution is based on the trick shown above. You can use the same trick to adjust a date to the first of a month, also to adjust the date to the first day of a year. And once you have this date, just add 1 year and subtract 1 day. You now have the last day in a year.
How to Get the Month Name from an Integer Value?
DECLARE @input INT
SET @input = 3
SELECT DATENAME(MONTH,@input*28) AS CurrentMonth
CurrentMonth
——————————
March
(1 row(s) affected)
Why does this work? First, we’re taking advantage of the fact, that, with respect to our question, both 19000326 and 20050326 are equal. Both will return “March” as month name and that’s all we care about here. Next, the multiplication * 28 is used to ensure that we will always hit a day in the correct month of @input. Try it out!
That’s it. I hope, you’ve seen that the DATETIME datatype is far from being mysterious. SQL Server provides a rich arsenal of build-in functions to deal efficiently with DATETIMEs. Use it!
]]>