Press "Enter" to skip to content

Category: Dates and Numbers

At Time Zone

Published 2016-07-29 by Kevin Feasel

Rob Farley looks at the AT TIME ZONE operation in SQL Server 2016:

But despite the longwindedness of it, I love it, because at no point did I need to figure out that Adelaide was in +10:30, or that Eastern was -5:00 – I simply needed to know the time zone by name. Figuring out whether daylight saving should apply or not was handled for me.

It works by using the Windows registry, which has all that information in it, but sadly, it’s not perfect when looking back in time. Australia changed the dates in 2008, and the US changed its dates in 2005 – both countries saving daylight for more of the year. AT TIME ZONE understands this. But it doesn’t seem to appreciate that in Australia in the year 2000, thanks to the Sydney Olympics, Australia started daylight saving about two months earlier. This is a little frustrating, but it’s not SQL’s fault – we need to blame Windows for that. I guess the Windows registry doesn’t remember the hotfix that went around that year. (Note to self: I might need to ask someone in the Windows team to fix that…)

Like most companies dealing with multiple time zones, we ended up building a table and function to translate.  This is a nice, built-in way of doing something very similar.

Comments closed

DATEFROMPARTS

Published 2016-07-07 by Kevin Feasel

Aaron Bertrand looks at the DATEFROMPARTS function in SQL Server 2012 and later:

The point of these functions is to make it easier to construct a date, or datetime, or datetime2 variable, when you know the individual parts. DATEFROMPARTS() takes three arguments: year, month, and day, and returns adate value. So, for example, SELECT DATEFROMPARTS(2016,7,6); would yield the date 2016-07-06.

Read on for a comparison of this function against about a dozen other methods of building dates from components.

Comments closed

Finding Dates

Published 2016-06-29 by Kevin Feasel

Derik Hammer shows the right way and the wrong way of using date functions in a WHERE clause:

I then changed my thought process to find the age of a 65 year old who’s birth day is today. Then I compare the DateOfBirth column to that static value.

SELECT COUNT(*) FROM dbo.Person p WHERE DateOfBirth <= DATEADD(YEAR,-65,GETDATE())

With the above query I bought myself an index seek and 345 logical reads. That works out to <3% of the cost.

This is true not just for date functions, but rather is applicable to almost all scalar functions.

Comments closed

DATEADD With SYSUTCDATETIME

Published 2016-04-12 by Kevin Feasel

Aaron Bertrand blogs on an estimation failure with DATEADD and SYSDATETIME/SYSUTCDATETIME:

Essentially, the problem is that a poor estimate can be made not simply when SYSDATETIME() (or SYSUTCDATETIME()) appears, as Erland originally reported, but when any datetime2expression is involved in the predicate (and perhaps only when DATEADD() is also used). And it can go both ways – if we swap >= for <=, the estimate becomes the whole table, so it seems that the optimizer is looking at the SYSDATETIME() value as a constant, and completely ignoring any operations like DATEADD() that are performed against it.

Paul shared that the workaround is simply to use a datetime equivalent when calculating the date, before converting it to the proper data type. In this case, we can swap outSYSUTCDATETIME() and change it to GETUTCDATE()

I suppose switching to GETUTCDATE isn’t too much of a loss, but it looks like (according to Paul White in the second linked Connect item) this appears to have been fixed in SQL Server 2014.

Comments closed

Using Dates And Times

Published 2016-04-07 by Kevin Feasel

Aaron Bertrand has an intro-level post on using dates and times in SQL Server:

I urge you to always use yyyymmdd (without the dashes) for a date without time – it will never fail, regardless of regional, language, or dateformat settings, and across any of the date/time data types. (And absolutely do not store it as a string data type in SQL Server – always store it as a proper date or time data type.)

This was a big one for me because I tend to use yyyy-mm-dd.

Comments closed

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