Image via WikipediaGiven 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
|
4/5/2010
|
|
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
|
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
|
mm-dd-yy
|
|
11
|
111
|
yy/mm/dd
|
|
12
|
112
|
ISO
|
yymmdd
yyyymmdd
|
-
|
13 or 113 (1,2)
|
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.
No comments:
Post a Comment