Formatting Dates
From Wiki
According to Joe Celko this should always happen on the client side, but in case you ever need it (for example in DTS when you have to output to a file) here is the SQL code.
This part will return a 4 digit year. We are using format values greater than 100
- DECLARE @d DATETIME
- SELECT @d = GETDATE()
- SELECT @d AS OriginalDate,
- CONVERT(VARCHAR,@d,100) AS ConvertedDate,
- 100 AS FormatValue,
- 'mon dd yyyy hh:miAM (or PM)' AS OutputFormat
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,101),101,'mm/dd/yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,102),102,'yyyy.mm.dd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,103),103,'dd/mm/yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,104),104,'dd.mm.yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,105),105,'dd-mm-yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,106),106,'dd mon yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,107),107,'Mon dd, yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,108),108,'hh:mm:ss'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,110),110,'mm-dd-yyyy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,111),111,'yyyy/mm/dd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,112),112,'yyyymmdd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,114),114,'hh:mi:ss:mmm(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'
This part will return mostly a 2 digit year (except for 9, 20 and 21). We are using format values less than 100
- DECLARE @d DATETIME
- SELECT @d = GETDATE()
- SELECT @d AS OriginalDate,
- CONVERT(VARCHAR,@d,0) AS ConvertedDate,
- 0 AS FormatValue,
- 'mon dd yyyy hh:miAM (or PM)' AS OutputFormat
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,1),1,'mm/dd/yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,2),2,'yy.mm.dd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,3),3,'dd/mm/yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,4),4,'dd.mm.yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,5),5,'dd-mm-yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,6),6,'dd mon yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,7),7,'Mon dd, yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,8),8,'hh:mm:ss'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,9),9,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,11),11,'mm-dd-yy'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,11),11,'yy/mm/dd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,12),12,'yymmdd'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,13),13,'dd mon yyyy hh:mm:ss:mmm(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,14),14,'hh:mi:ss:mmm(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,20),20,'yyyy-mm-dd hh:mi:ss(24h)'
- UNION all
- SELECT @d,CONVERT(VARCHAR,@d,21),21,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
Contributed by: --SQLDenis 17:05, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Dates
