SQL Server Code,Tips and Tricks, Performance Tuning

What do you think will be the output?


DECLARE @d datetime
SET @d = '20071010'

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

After that run this to see how to get first and last days for years, quarters, months and weeks(be careful of ISO 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

It looks like SQL Server 2008 has nanosecond + microseconds precision for the time datatype
If you run the following

[edit]I just looked at BOL and yes nanoseconds = ns, microsecond = mcs when used in dateadd[/edit]

DECLARE @t time
SELECT @t ='0:0'
SELECT @t AS Time1,DATEADD(ms,1,@t) AS TimeMilli,
DATEADD(ns,10000,@t) AS TimeNano1,DATEADD(ns,100,@t) AS TimeNano2


Time1 00:00:00.0000000
TimeMilli 00:00:00.0010000
TimeNano1 00:00:00.0000100
TimeNano2 00:00:00.0000001

Another interesting thing is that you can not use 0,'0' or ' ' to assign a value

These 3 will all fail

DECLARE @t time
SELECT @t =' '

DECLARE @t time
SELECT @t ='0'


DECLARE @t time
SELECT @t =0

But this will succeed

DECLARE @t time
SELECT @ =''

Okay one more quick teaser

You have this date '2007-01-01 00:00:00.001'

When adding 1 or 2 milliseconds to that date what will be the result?

SELECT DATEADD(ms,1,CONVERT(datetime, '2007-01-01 00:00:00.001'))

SELECT DATEADD(ms,2,CONVERT(datetime, '2007-01-01 00:00:00.001'))