SQL teaser: guess the output

[SQLDenis]

what does this display?

declare @d datetime 
set @d = '20071010' 

select dateadd(yy, datediff(yy, 0, @d)+1, -1)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Spoiler

12/31/07

Because your adding 108 years to date -1 (which is 12/31/1899 if date 0 is 1/1/1900)

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[philhege]

Leave it to Denis to bring up all that historical stuff from before the beginning of SQL time.

 

[SQLDenis]

some fun stuff

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

Denis

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

what about last day of previous month.

SELECT DateAdd(Month, DateDiff(Month, 0, getdate()), -1)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

what about last day of previous month.

That was then, this is now

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

I never knew you guys were such history buffs...

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[ptheriault]

If we don't know where we've been how can we tell where we're going?


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[AlexCuse]

I don't wanna know where I'm going!

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[ptheriault]

It's very clear where your heading Alex [Wink]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

If we don't know where we've been how can we tell where we're going?

very easy, by getting sober and getting rid of those blackouts

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[philhege]

Window seat, please, Alex.