70-461 – notes for ch. 2 – dates

frontis

Different date precisions

Trying out different styles of date in sql server. The following…


select getdate()
select CURRENT_TIMESTAMP
select SYSDATETIME()
select SYSDATETIMEOFFSET()
select CAST(SYSDATETIME() AS DATE)
select GETUTCDATE()

…gives

2015-01-08 12:54:04.310
2015-01-08 12:54:04.310
2015-01-08 12:54:04.31
2015-01-08 12:54:04.3131631 +00:00
2015-01-08
2015-01-08 13:01:21.077

Datepart

Datepart works as follows:


select DATEPART(year, getdate())
select DATEPART(MONTH, getdate())
select DATEPART(DAY, getdate())
select DATEPART(QUARTER, getdate())


…returning the numerics you’d expect

Datename

The Datename function does something similar but it returns a string rather than an integer. For most of the dateparts the only difference is the datatype returned rather than the value:

select datename(day, getdate())
select datename(week, getdate())
select datename(hour, getdate())
select datename(minute, getdate())
select datename(second, getdate())
select datename(millisecond, getdate())
select datename(microsecond, getdate())
select datename(nanosecond, getdate())
select datename(ISO_WEEK , getdate())

…gives:

23
4
12
33
48
840
840000
840000000
4

Datefromparts does what you would expect:


declare @a date
select @a = DATEFROMPARTS(1977, 08, 16)
select @a

….returns

1977-08-16

If it’s not valid:


declare @a date
select @a = DATEFROMPARTS(1977, 16, 08)
select @a

…you get this error:

Msg 289, Level 16, State 1, Line 2
Cannot construct data type date, some of the arguments have values which are not valid.

End of month

This might be handy:

select EOMONTH(SYSDATETIME())

Start of month

There isn’t a SOMONTH

Msg 195, Level 15, State 10, Line 1
'SOMONTH' is not a recognized built-in function name.

but I guess this works:


select convert(date, dateadd(day, (1 + -1 * DATEPART(day, SYSDATETIME())), SYSDATETIME()))

no quotes

I found the use of date and day, without quotes a bit
counterintuitive. If you try to include quotes you get the following


1> select dateadd('DAY', SYSDATETIME(), -5)
2> go
Msg 1023, Level 15, State 1, Server server1, Line 1
Invalid parameter 1 specified for dateadd.

Datediff

Date diff returns the difference between the two dates specified. If the first
date is later, then it will be negative:

1> select DATEDIFF(year, '19770816', '19350108');
2> go


    -42

As the book says, ‘this function looks only at the parts from the requested one and above in the date and time hierarchy, so although the difference between the two dates below is closer to 64 years than 63 years, the function returns -63.


1> select DATEDIFF(year, '20090204', '19461021');
2> go


-63

Switchoffset

I wasn’t entirely sure of the point of:


select SWITCHOFFSET(SYSDATETIMEOFFSET(), '+01:00')
go

…in that you could just add an hour by doing


1> select dateadd(hour, 1, getdate())
2> go


2015-01-08 16:59:17.230

…but if you try converting that, then you lose the fact that it’s offset:


1> select convert(datetimeoffset, dateadd(hour, 1, getdate()))
2> go


       2015-01-08 17:00:43.6930000 +00:00

You can, though, use this:


1> select todatetimeoffset(dateadd(hour, 1, getdate()), '+01:00')
2> go


           2015-01-08 17:04:11.680 +01:00

I found that you need to pad the ‘hour bit’ of the time offset with a ‘0’. Otherwise you get this error:

2> select SWITCHOFFSET(SYSDATETIMEOFFSET(), '+1:00')
3> go
Msg 9812, Level 16, State 1, Server xxx, Line 2
The timezone provided to builtin function switchoffset is invalid.

Advertisements