Login or Sign Up to become a member!

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Data formatting dates

From Wiki

Jump to: navigation, search

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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 02:48, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

Currently 1.14/5
1
2
3
4
5

171 Rating: 1.1/5 (7 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Data_formatting_dates"