THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser: Guess the output

What do you think will be the output?

DECLARE @d datetime
SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)
Published Wednesday, October 10, 2007 11:46 AM by Denis Gobo



Stephen Mandeville said:

DECLARE @d datetime

SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1)

SELECT DATEDIFF(yy, 0,'20071010') = 107

-- 0 = '1900-01-01'

SELECT DATEADD(yy, 108, -1)

-- Last Day of Year

'2007-12-31 00:00:00.000'

October 10, 2007 11:51 AM

Adam Machanic said:

Nice one.  The -1 tripped me up...

October 10, 2007 11:54 AM

David R Buckingham said:

And conversely, the same/similar trick will yield the first day of the year:

DECLARE @d datetime

SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0)

October 10, 2007 12:55 PM

Denis Gobo said:

Yes, and with that we can also do the qurters, months and weeks

DECLARE @d datetime

SET @d = '20071010'

SELECT DATEADD(yy, DATEDIFF(yy, 0, @d), 0) as FirstDayOfYear,

DATEADD(yy, DATEDIFF(yy, 0, @d)+1, -1) as LastDayOfYear,

DATEADD(qq, DATEDIFF(qq, 0, @d), 0) as FirstDayOfQuarter,

DATEADD(qq, DATEDIFF(qq, 0, @d)+1, -1) as LastDayOfQuarter,

DATEADD(mm, DATEDIFF(mm, 0, @d), 0) as FirstDayOfMonth,

DATEADD(mm, DATEDIFF(mm, 0, @d)+1, -1) as LastDayOfMonth,

DATEADD(wk, DATEDIFF(wk, 0, @d), 0) as FirstDayOfWeek,

DATEADD(wk, DATEDIFF(wk, 0, @d)+1, -1) as LastDayOfWeek


October 10, 2007 1:05 PM

David Markle said:

A pink slip.

October 10, 2007 3:09 PM

Madhivanan said:

This would also give last day of year

SELECT DATEADD(year, DATEDIFF(year, -1, @d), -1)

October 11, 2007 1:24 AM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement