[SQL Server] Liste von über die CONVERT-Funktion erzeugbaren Datumsformaten

[Thomas Darimont]

Hallo,

die Liste mit den unterstützten Datumsformaten findet man hier:
http://msdn.microsoft.com/de-de/library/ms187928.aspx

WITH initial_data AS
  (SELECT 
	-1				   AS sep_idx,
    ''                 AS item ,
    '1,2,3,4,5,6,7,8,10,11,12,14,0,100,101,102,103,104,105,106,107,108,9,109,110,111,112,13,113,114,20,120,21,121,126,127,130,131' AS data,
    ','                AS separator
  ) ,
  vals(sep_index,item, data, separator) AS
  (SELECT sep_idx,
    CAST(item AS           VARCHAR(255)),
    CAST(data+separator AS VARCHAR(4000)),
    separator
  FROM initial_data
  UNION ALL
  SELECT charindex(separator,data,0) ,
    CAST(LEFT(data,charindex(separator,data,1)-1) AS VARCHAR(255)) ,
    CAST(RIGHT(data,LEN(data)-charindex(separator,data,1)) AS VARCHAR(4000)) ,
    separator
  FROM vals
  WHERE charindex(separator,data,0) > 0
  )
SELECT getdate(),cast(item as int) fromat_nr,CONVERT(VARCHAR(64),GETDATE(),cast(item as int)) FROM vals

WHERE sep_index >= 0 OPTION (MAXRECURSION 0)

Ausgabe:

datum                                 format_nr   formatted_date
---------------------------------------------------------
2012-06-08 15:30:05.580	1	06/08/12
2012-06-08 15:30:05.580	2	12.06.08
2012-06-08 15:30:05.580	3	08/06/12
2012-06-08 15:30:05.580	4	08.06.12
2012-06-08 15:30:05.580	5	08-06-12
2012-06-08 15:30:05.580	6	08 Jun 12
2012-06-08 15:30:05.580	7	Jun 08, 12
2012-06-08 15:30:05.580	8	15:30:05
2012-06-08 15:30:05.580	10	06-08-12
2012-06-08 15:30:05.580	11	12/06/08
2012-06-08 15:30:05.580	12	120608
2012-06-08 15:30:05.580	14	15:30:05:580
2012-06-08 15:30:05.580	0	Jun  8 2012  3:30PM
2012-06-08 15:30:05.580	100	Jun  8 2012  3:30PM
2012-06-08 15:30:05.580	101	06/08/2012
2012-06-08 15:30:05.580	102	2012.06.08
2012-06-08 15:30:05.580	103	08/06/2012
2012-06-08 15:30:05.580	104	08.06.2012
2012-06-08 15:30:05.580	105	08-06-2012
2012-06-08 15:30:05.580	106	08 Jun 2012
2012-06-08 15:30:05.580	107	Jun 08, 2012
2012-06-08 15:30:05.580	108	15:30:05
2012-06-08 15:30:05.580	9	Jun  8 2012  3:30:05:580PM
2012-06-08 15:30:05.580	109	Jun  8 2012  3:30:05:580PM
2012-06-08 15:30:05.580	110	06-08-2012
2012-06-08 15:30:05.580	111	2012/06/08
2012-06-08 15:30:05.580	112	20120608
2012-06-08 15:30:05.580	13	08 Jun 2012 15:30:05:580
2012-06-08 15:30:05.580	113	08 Jun 2012 15:30:05:580
2012-06-08 15:30:05.580	114	15:30:05:580
2012-06-08 15:30:05.580	20	2012-06-08 15:30:05
2012-06-08 15:30:05.580	120	2012-06-08 15:30:05
2012-06-08 15:30:05.580	21	2012-06-08 15:30:05.580
2012-06-08 15:30:05.580	121	2012-06-08 15:30:05.580
2012-06-08 15:30:05.580	126	2012-06-08T15:30:05.580
2012-06-08 15:30:05.580	127	2012-06-08T15:30:05.580
2012-06-08 15:30:05.580	130	19 ****? 1433  3:30:05:580PM
2012-06-08 15:30:05.580	131	19/07/1433  3:30:05:580PM

Gruß Tom