Featured Post

Evony 100,000 Prestige Noob

Let's set things straight right off; I do not think prestige is important for judging someone's abilities in game, it has its uses but for the most part it means nothing.  This guide is to explain how prestige works and how easily it can be gained.

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...