DATETIMEis an eight-byte datatype which stores both a date and time in one column, with an accuracy of three milliseconds. As we’ll see though, the distribution of this granularity may not be exactly what we’d expect.
DATETIMEvalues are January 1, 1753 00:00:00.000, through December 31, 9999 23:59:59.997. On older databases designed prior to SQL Server 2008, because there was no explicit support for date values, it was sometimes customary to leave off the time portion of a
DATETIMEvalue, and have it default to midnight on that morning. So for example today would be stored as
February 21, 2018 00:00:00.000.
If you’re not particularly familiar with SQL Server data types, this is detailed enough information to get you going and to explain exactly why you shouldn’t use DATETIME anymore…
Given the following DDLCREATE TABLE dbo.IntToTime ( CREATE_TIME int );
What will be the result of issuing the following command?ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;
Clearly, if I’m asking, it’s not what you might expect.
Click through if you have not memorized your implicit conversion tables.
The worst are by far the string representation, as there is no actual check on the contents. It can literally contain everything. And is ’01/02/2018′ the first of February 2018 (like any sane person would read, because days come before months), or the 2nd of January? So if you have to store dates in your data warehouse, avoid strings at all costs. No excuses.
The integer representation – e.g. 20171208 – is really popular. If I recall Kimball correctly, he said it’s the one exception where you can use smart keys, aka surrogate keys that have a meaning embedded into them. I used them for quite some time, but I believe I have found a better alternative: using the actual date data type.
I bounce back and forth, but I’m sympathetic to Koen’s argument, which you can read by clicking through.
flipTimeprovides utilities for working with time series and date-time data. The package can be installed from
I will discuss only two functions from the package in this post,
AsDateTime().These are used for the conversion of date and date-time strings, respectively. These functions build on the convenience and speed of the
lubridatefunction. Furthermore, the
flipTimefunctions provide additional functionality (making them easier to use). The functions are smart about identifying the proper format to use. So the user doesn’t need to specify the format(s) as inputs. At the same time, both
AsDateTime()are careful to not convert any strings to dates when they are not formatted as dates. Additionally, it will also warn the user when the dates are not in an unambiguous format.
Check it out.
Displaying dates and times with different formats in TSQL is a task I run into quite a bit. I used to visit this page so many times, I’m surprised it doesn’t have a “Welcome back, Dave!” banner on it at the top. After umpteen million times, I decided it was time to be more efficient. I created this query that’s come in handy numerous times. I considered dumping it into a view, but I’ve found it’s nice to copy/paste the CONVERT statement (directly from a script) and replace CURRENT_TIMESTAMP with whatever column I want to have formatted.
Click through for the script and sample output.
I assumed that the
DATEDIFFfunction I wrote worked this way: Subtract the two dates to get a timespan value and then return the number of seconds (rounded somehow) in that timespan.
But that’s not how it works. The docs for
“Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.”
There’s no rounding involved. It just counts the ticks on the clock that are heard during a given timespan.
Read the whole thing.
Time Zones were definitely being a drag today. I got an email from one of the developers at work asking about the performance difference between 2 queries. The only difference between the 2 queries is that one of them uses the AT TIME ZONE clause that was added in SQL Server 2016. I have not played around with this particular clause, but we do store quite a bit of data in the datetimeoffset data type. In the table in the developer’s queries, the data is all stored in the Eastern time zone, but they are considering storing it in additional time zones and will want to be able to display it in the Eastern time zone even if not stored that way. Thus, AT TIME ZONE.
When the developer was testing the conversion function, he noticed that the query slowed waaaayyyyy down when he added AT TIME ZONE. Before adding AT TIME ZONE to the query, STATISTICS TIME for the query was: CPU time: 145549 ms, elapsed time: 21693 ms.. It returned 8,996 rows, but if I removed the DISTINCT, it returned over 72M rows. That’s a lot of clams … er, data.
Read on for the rest of the story, including Robert’s solution. Also check out his Connect item related to this.
In my first 5 blogs on Temporal, I failed to note something pretty important about their usage. The start and end times for the row (and it follows, the historical rows) are likely not in your local time zone. They are stored in UTC time zone. This is obviously good because of that pesky daylight saving time issue where 1:30 AM occurs twice each year (here in the US, the time occurs on the first Sunday of November).
Unless you live in London, England or someplace where the offset from UTC is 0, it can make working with these tables confusing, because most people rarely work in UTC time, and even rarer is to think in UTC time when most of your data is likely in your local time zone. So you write your query and use local time…and then, most likely, data is returned…but not necessarily the data you actually desired.
Click through to see ways of translating those values.
To get public holidays live, you first need an API that is giving you up-to-date information. There are some web pages that has the list of public holidays. I have already explained in another blog post how to use a web page and query public holidays from there. That method uses custom functions as well, here you can read about that.
The method of reading data from a web page has an issue already; Web.Page function from Power Query is used to pull data from that page, and this function needs a gateway configuration to work. There is another function Xml.Document that can work even without the gateway. So because of this reason, we’ll use Xml.Document and get data from an API that provides the result set as XML.
WebCal.fi is a great free website with calendars for 36 countries which I do recommend for this example. This website, provides the calendars through XML format. There are other websites that give you the calendar details through a paid subscription. However, this website is a great free one which can be used for this example. WebCal.fi is created by User Point Inc.
This was an interesting approach to the problem, one I did not expect when first reading the article. I figured it’d be some sort of date calculation script.
I have a series of blogs about temporal tables in SQL Server 2016, which you can see here. However, one thing I never had mentioned was that the Temporal Row Start and End Times are always stored in UTC times (which is generally inconvenient to work with unless you are from the Great Britain area). However, when you are looking at your data and want to see what the data looked like at ‘Yesterday at 12:00 at the computer you are working from, this can be a bit more work.
In 2016 SQL Server, a new feature was added that totally passed me by. AT TIME ZONE. It is used to manipulate the time zone offset of a point in time value,either to append the time zone to a value, or to shift the time zone value of a DATETIMEOFFSET value. The most common practical use that I will show is converting a value in your local time to UTC (and vice versa).
This is something I tend to forget about, but can be useful when building quick reports from UTC. I’d store all data in UTC, just to make sure dates and times are consistent across servers in different locations, but you don’t have to do those calculations in your head all the time.