Press "Enter" to skip to content

Category: Dates and Numbers

AT TIME ZONE

Published 2016-03-16 by Kevin Feasel

Tom LaRock disusses the AT TIME ZONE function in SQL Server 2016:

Of course you will need to know what is allowed for you to use for the time zone name. Fortunately for us, this list is stored in the registry of the server. In other words, you can use whatever timezones are installed on the server. For a complete list you can query the sys.time_zone_info DMV:

If you work at a company with international dealings, you probably already have a time zone table somewhere, but this is a nice way of encapsulating possibly-slow time zone conversion and calculation operations.

Comments closed

Defaults Not Guaranteed Equal

Published 2016-01-28 by Kevin Feasel

Michael J. Swart shows that two DATETIME2 columns with default constraints will not necessarily show the same value upon insertion:

If I want to depend on these values being exactly the same, I can’t count on the default values.

Default constraints will fill in the correct value, but as Michael notes, “the correct value” is calculated each time.  Also, note that his results are about a millisecond off, so if you’re just using DATETIME, the frequency of observation of this occurrence will be lower, as DATETIME is only good to 3 milliseconds.  That’s not a good reason to use DATETIME, though.

Comments closed

Days Of The Week

Published 2015-11-18 by Kevin Feasel

Tony Rogerson shows us how to get the fourth Saturday of the month, among other things:

You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here: http://stackoverflow.com/questions/33694768/how-to-get-list-of-2nd-and-4th-saturday-dates-in-sql-server.

I’ve created it as a Table Valued Function so you can bind it into any query you wish.

Tony created a Table-Valued Function, which is handy but leads me to the classic User-Defined Function reminder:  they tend to cause performance problems. One alternative is a dedicated date table with attributes like day of week and nth day of month.

Comments closed