Demystifying the SQL Server DATETIME Datatype

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.

Continues…

Leave a comment

Your email address will not be published.