Derrick Leggett Blog

I feel like posting today.  :)  We get asked about datetime formats a lot on the forums.  Here is a simple little script to tell you what formats SQL Server supports using the CONVERT function. 

SET NOCOUNT ON

CREATE TABLE #results(
 conversion INT,
 result VARCHAR(55),
 code VARCHAR(255)) 

DECLARE
 @min INT,
 @max INT,
 @date DATETIME

SELECT
 @min = 1,
 @max = 131,
 @date = GETDATE()

WHILE @min <= @max
BEGIN

 IF @min BETWEEN 15 AND 19
  OR @min  = 26
  OR @min BETWEEN 27 AND 99
  OR @min BETWEEN 115 AND 119
  OR @min BETWEEN 122 AND 125
  OR @min BETWEEN 127 AND 129
  BEGIN
   GOTO NEXT_LOOP
  END

 INSERT #results(
  conversion,
  result,
  code)

  SELECT
   @min,
   CONVERT(VARCHAR,@date,@min),
   'SELECT CONVERT(VARCHAR,GETDATE(),' + CAST(@min AS VARCHAR(5)) + ')'

NEXT_LOOP:

SELECT @min = @min + 1
END

SELECT
 @date AS datetime_format,
 conversion,
 result,
 code
FROM #results

DROP TABLE #results