How Can I Find the Last Day of the Month of a Given Date?
Well, following from the previous discussion, you could possibly do this:
DECLARE @dt DATETIME
SET @dt = ‘20050326’
SELECT CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1
THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+31 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1
THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+30 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1
THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+29 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1
THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+28 AS CHAR(8)) AS DATETIME)
END
AS LastDayOfMonth LastDayOfMonth
——————————————————
2005-03-31 00:00:00.000
(1 row(s) affected)
But honestly, this is certainly far from being elegant. This one’s better:
DECLARE @dt DATETIME
SET @dt = ‘20050325’
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))) AS LastDayOfMonth LastDayOfMonth
——————————————————
2005-03-31 00:00:00.000
(1 row(s) affected)
Or, in it’s shorter version:
SELECT DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1 AS LastDayOfMonth
LastDayOfMonth
——————————————————
2005-03-31 00:00:00.000
(1 row(s) affected)
The miracle again, is the adjustment to the first day in the month of the date given via the second DATEADD() function. Then we add 1 month and subtract 1 day. Done!
Last one here:
declare @31st datetime
set @31st = ‘19341031’ — any 31st
declare @now datetime set @now = ‘20050325’
select dateadd(month,datediff(month,@31st,@now),@31st)
——————————————————
2005-03-31 00:00:00.000
(1 row(s) affected)
Again, a pretty cool script for lazycoders by SQL Server MVP Steve Kass. Most interesting about his solution is, that it will work with any 31st (this is mandatory!!!), no matter if past, present or future. And honestly, it’s much faster to type.
How to Determine a Person’s Age?
Definitely more than one way to skin that cat! Here are only some variations in no particular order:
DECLARE @d DATETIME
SET @d = ‘19680723’
SELECT DATEDIFF(yyyy,@d,GETDATE()) – (CASE WHEN DATEADD(yyyy,DATEDIFF(yyyy,@d,GETDATE()),@d) > GETDATE() THEN 1 ELSE 0 END)
SELECT DATEDIFF(yy, @d, GETDATE()) – CASE WHEN (MONTH(GETDATE()) * 100 + DAY(GETDATE())) < (MONTH(@d)* 100 + DAY(@d))
THEN 1 ELSE 0 END SELECT (CAST(CONVERT(CHAR(8),GETDATE(), 112) AS INT) – CAST(CONVERT(CHAR(8), @d, 112) AS INT))/10000
SELECT YEAR(GETDATE()) – YEAR(@d) – (CASE WHEN (MONTH(@d) > MONTH(GETDATE())) OR (MONTH(@d) = MONTH(GETDATE()) AND DAY(@d) > DAY(GETDATE()))
THEN 1 ELSE 0
END)
SELECT (0+CONVERT(CHAR(8),GETDATE(), 112) – CONVERT(CHAR(8), @d, 112))/10000
Result is always the same. I won’t go into explanation how each and every single method works as this would blow up this article too much. Try this for yourself, and if there are still questions remaining, feel free to contact me via the forum.