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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



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

Leave a Comment


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