Featured Posts

Web Presense Solution I've officially started up my independent web development side-business. I am hoping to bring quite a few small businesses into the present with a simple, cost-effective package that provides them with...

Readmore

MS SQL date / time conversion 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...

Readmore

jQuery Delay Happy Note: I was contacted by the jQuery team and my code is being added to the core functionality; that really made my day!  You will want to remove the current code from your site if you plan on upgrading...

Readmore

ASP Search Stemmer Class The original stemmer class was developed by Martin Porter to bring words back to their word stems. For example "abilities" would stem to "able", "smelling" to "smell", "I'm awesome" to "damn straight",...

Readmore

  • Prev
  • Next

MS SQL date / time conversion

Posted on : 25-09-2009 | By : chelfers | In : SQL, Web

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 with ms',
CONVERT( varchar, getdate(), 110 ) AS '110 USA',
CONVERT( varchar, getdate(), 111 ) AS '111 Japanese',
CONVERT( varchar, getdate(), 112 ) AS '112 ISO',
CONVERT( varchar, getdate(), 113 ) AS '113 Europe default with ms',
CONVERT( varchar, getdate(), 114 ) AS '114',
CONVERT( varchar, getdate(), 120 ) AS '120 ODBC canonical',
CONVERT( varchar, getdate(), 121 ) AS '121 ODBC canonical (with ms)',
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!

Read Full Article