Mahmoud ElMansy: knowledge meant to be free

How to format datetime & date in Sql Server

Run the following queries to show various types of date time formats
in SQL server

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/yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,102),102,'yy.mm.dd'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,103),103,'dd/mm/yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,104),104,'dd.mm.yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,105),105,'dd-mm-yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,106),106,'dd mon yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,107),107,'Mon dd, yy'
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-yy'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,111),111,'yy/mm/dd'
UNION ALL
SELECT @d,CONVERT(VARCHAR,@d,112),112,'yymmdd'
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)'

and here is the output