Featured Post

jQuery and Forms

On my latest searches for various ways jQuery interacts with the DOM, and more importantly forms for my recent project I've found that various sites could answer some of my questions but most focused on single form elements and not much more. jQuery can access elements in quite a few different ways,...

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