The many faces of Datetime

Image via Wikipedia

As report writing professionals we, on a daily basis, have Ugo Boncompagni aka Pope Gregory XIII  to thank for a consistent and repeating annoyance.  Popes over the years have been attributed to bringing many weird and wonderful revelations to the world, this one in particular landed us with our calendar.  Good old Ugo.

Given that it has been knocking around since 1582, it's not doing too bad a job.  In fact it behaved itself for around 388 years, which is when things went wrong.  Fast forward to the beginning of the 1970s, IBM, no doubt binding dark forces together, developed a new computer language called SQL (known as SEQUEL at the time - but had to change it to avoid trademark infringement).

As Mr Boncompagni's calendar was slowly adopted across the globe, there were some countries who just had to be different.  You know the type of countries - those who never tucked their school shirts in and wore their tie all wrong.  So today, although we are using the same calendar, we are left with a dozen ways to write a date depending on where you are stood.

'So - what has that got to do with me?' I hear you asking.

If you are working on one or more databases that handle dates which only handles data relevant for your own country, and the database environment has been installed with all date related default settings aligned correctly then you should (mostly) be fine and don't need to read further. However, if you find yourselves working on international data then you may find something of interest.

Randomly picking a date from the calendar, say 5th April, you can write it:

Type	Format
Long Date	5th, April 2010
Short Date	5th Apr 2010
British/French	5/4/2010
U.S.	4/5/2010
Japan	10/4/5

And a number of other ways also.

As you can see - a date can quickly lack any meaning depending on how it is presented.  As a preference I prefer using the long date, as it removes any question of doubt (defaulting to the short date if space is an issue).  However, opinions differ and more often than not you will need to present it as dd/mm/yy or yy/mm/dd etc.

In the aforementioned SQL, since MS SQL 2000, you are able to use the CONVERT function to handle your date formatting.

SYNTAX
======
CONVERT( {data_type} [ (length) ] , expression [ ,style ] )
ie
SELECT CONVERT(nvarchar(20) ,getdate() ,103)

The style argument is the one of interest to us.  The resultant data_type will have the date formatted as dictated by the style.  Using this function is a rather effective way to remove the time part from a datatime.

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113 (1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

There is also a CAST function, however you can not handle styles with it - so is not much use here.

So, say you have a datetime

SELECT CONVERT(datetime,'2010-06-17 01:23:45.000') as myDate

Gives you:

myDate
2010-06-17 01:23:45.000

However, you don't want the time and you want it in, say British/French format

SELECT CONVERT(nvarchar(20),CONVERT(datetime,'2010-06-17 00:00:00.000'),103) as myDate

Gives you:

myDate
17/06/2010

This proves particularly useful if you need to summarise by a date and have several times that need to be grouped together.

Another way to achieve the removal of a timestamp from datetime is :

select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

which is also a popular and fast way of doing it.