Annoying Date Formats

Randolph West shows that even The Best Date Format can deceive you under certain circumstances:

Look carefully. DATE and DATETIME2 are showing the date of 12 July 2017 as expected. Unfortunately, the DATETIME and SMALLDATETIME data types are showing a date of 7 December 2017.

That’s not good at all. It means that the ISO 8601 standard does not work the way we might expect it to. The reason is simple, if annoying: we need to add a time to the date to make it pass the ISO 8601 standard.

I don’t like the idea of having to write 20170713 instead of 2017-07-13, but that is the only date format in SQL Server that I’ve run across that will work with any language and culture settings.

Related Posts

STOP Date Formats

Dave Mason notes that the STOPAT date option when restoring a log backup is temperamental: There’s nothing I see in the documentation regarding the format for “time“. But there are a couple of examples, including this one: RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM'; That string looks […]

Read More

Avoid Ticks

Michael J. Swart shows you how to convert DATETIME2 values to Ticks: A .Net tick is a duration of time lasting 0.1 microseconds. When you look at the Tick property of DateTime, you’ll see that it represents the number of ticks since January 1st 0001. But why 0.1 microseconds? According to stackoverflow user CodesInChaos “ticks are simply the […]

Read More

2 Comments

  • John Spencer on 2017-07-17

    Yes, I (along with many others [I am sure], have run into this thing of ‘annoying’ date formats. The ‘tried and true for me has also been the YYYYDDMM format. And when including the time–using the 24 hour time format of HHMMSS has also been the ‘only’ way I have been able to have things work as expected.
    Yes, I have made ‘assumptions’ in the past — and have been burned. Thus, that absolutely fitting ‘annoyance’ word.

    Good article for the ‘unwary’

  • Ed Eaglehouse on 2017-07-18

    Yet another example of Microsoft’s disrespect for standards, to the detriment of its customers. The ISO-8601 standard works; Microsoft just didn’t implement it correctly, as usual.

Leave a Reply

Your email address will not be published. Required fields are marked *

Categories

July 2017
MTWTFSS
« Jun  
 12
3456789
10111213141516
17181920212223
24252627282930
31