Featured Post

Javascript href Voids and More

- If you are a web developer raise your hand. *raises hand* - If you have ever referenced Javascript for a link address raise your hand. *raises hand* I'm sure there are many guilty developers out in the wild who use the age old classic <a href="javascript:function();">Click Here</a> A...

Read More

MS SQL date / time conversion

Posted by chelfers | Posted in SQL, Web | Posted on 25-09-2009

Tags: , , , , ,

0

I often find myself turning to MSDN to get the possible conversion options on MS SQL's date data type, so I finally became un-lazy and created a little script to use in the future to quickly list all possibilities with nice example data and name references.

SELECT
CONVERT( varchar, GetDate(), 100 ) AS '100 Default',
CONVERT( varchar, GetDate(), 101 ) AS '101 USA',
CONVERT( varchar, GetDate(), 102 ) AS '102 ANSI',
CONVERT( varchar, GetDate(), 103 ) AS '103 British/French',
CONVERT( varchar, GetDate(), 104 ) AS '104 German',
CONVERT( varchar, GetDate(), 105 ) AS '105 Italian',
CONVERT( varchar, GetDate(), 106 ) AS '106',
CONVERT( varchar, GetDate(), 107 ) AS '107',
CONVERT( varchar, GetDate(), 108 ) AS '108',
CONVERT( varchar, GetDate(), 109 ) AS '109 Default + milliseconds',
CONVERT( varchar, GetDate(), 110 ) AS '110 USA',
CONVERT( varchar, GetDate(), 111 ) AS '111 JAPAN',
CONVERT( varchar, GetDate(), 112 ) AS '112 ISO',
CONVERT( varchar, GetDate(), 113 ) AS '113 Europe default + milliseconds',
CONVERT( varchar, GetDate(), 114 ) AS '114',
CONVERT( varchar, GetDate(), 120 ) AS '120 ODBC canonical',
CONVERT( varchar, GetDate(), 121 ) AS '121 ODBC canonical (with milliseconds)',
CONVERT( varchar, GetDate(), 126 ) AS '126 ISO8601',
CONVERT( varchar, GetDate(), 130 ) AS '130 Hijri',
CONVERT( varchar, GetDate(), 131 ) AS '131 Hijri'

Pretty dang simple if you ask me; once you find the format you want take a look at the column name and reference it back to the code.

Enjoy!

Continue reading...