SQL date time formatting / The Lazy Developer

When writing queries you might sometimes come across those people clients who want the dates formatted in a certain way (pesky clients *shakes fist). Don’t stress its nothing too difficult.

Take the date 20th May 2014 and you get a request to convert this to dd/mm/yyyy.

We usually work in iso dates (yyyymmdd). For the example I will cast the iso date as a string and then cast it as a datetime and work from there.

Our final product will look something as follows:

--
--
select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)
--Output will be '20/05/2014'

select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)

--Output will be '20/05/2014'

I will go through what each of the steps entail.

--
--
-- First we will need to cast our integer as a varchar
select Cast(20140520 as varchar)
--Output will be 20140520 (obviously as a string)

--Next we will need to cast our varchar as datetime
select Cast(Cast(20140520 as varchar) as datetime)
--Output will be '2014-05-20 00:00:00.000'

--Now we will need to convert the datetime to a varchar
select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)
--Output will be '20/05/2014'

-- CONVERT(data_type , expression , style)
-- data_type = is the type you want to convert your expression to.
-- expression= the current expression
-- style = the style you want the expression converted to.
-- 0(yy) or 100(yyyy) = mon dd yyyy hh:miAM (or PM)
-- 1(yy) or 101(yyyy) = mm/dd/yy
-- 2(yy) or 102(yyyy) = yy.mm.dd
-- 3(yy) or 103(yyyy) = dd/mm/yy
-- 4(yy) or 104(yyyy) = dd.mm.yy
-- 5(yy) or 105(yyyy) = dd-mm-yy
-- 6(yy) or 106(yyyy) = dd mon yy
-- 7(yy) or 107(yyyy) = Mon dd, yy
-- 8(yy) or 108(yyyy) = hh:mm:ss
-- 9(yy) or 109(yyyy) = mon dd yyyy hh:mi:ss:mmmAM (or PM)
-- 10(yy) or 110(yyyy) = mm-dd-yy
-- 11(yy) or 111(yyyy) = yy/mm/dd
-- 12(yy) or 112(yyyy) = yymmdd
-- 13 or 113(yyyy) = dd mon yyyy hh:mi:ss:mmm (24h)
-- 14(yy) or 114(yyyy) = hh:mi:ss:mmm (24h)
-- 20 or 120(yyyy) = yyyy-mm-dd hh:mi:ss (24h)
-- 21 or 121(yyyy) = yyyy-mm-dd hh:mi:ss.mmm (24h)
-- 126(yyyy) = yyyy-mm-ddThh:mi:ss.mmm (no spaces)
--130(yyyy) = dd mon yyyy hh:mi:ss:mmmAM
--131(yyyy) = dd/mm/yy hh:mi:ss:mmmAM

-- First we will need to cast our integer as a varchar

select Cast(20140520 as varchar)

--Output will be 20140520 (obviously as a string)

--Next we will need to cast our varchar as datetime

select Cast(Cast(20140520 as varchar) as datetime)

--Output will be '2014-05-20 00:00:00.000'

--Now we will need to convert the datetime to a varchar

select Convert(varchar,Cast(Cast(20140520 as varchar) as datetime),103)

--Output will be '20/05/2014'

-- CONVERT(data_type , expression , style)

-- data_type = is the type you want to convert your expression to.

-- expression= the current expression

-- style = the style you want the expression converted to.

-- 0(yy) or 100(yyyy) = mon dd yyyy hh:miAM (or PM)

-- 1(yy) or 101(yyyy) = mm/dd/yy

-- 2(yy) or 102(yyyy) = yy.mm.dd

-- 3(yy) or 103(yyyy) = dd/mm/yy

-- 4(yy) or 104(yyyy) = dd.mm.yy

-- 5(yy) or 105(yyyy) = dd-mm-yy

-- 6(yy) or 106(yyyy) = dd mon yy

-- 7(yy) or 107(yyyy) = Mon dd, yy

-- 8(yy) or 108(yyyy) = hh:mm:ss

-- 9(yy) or 109(yyyy) = mon dd yyyy hh:mi:ss:mmmAM (or PM)

-- 10(yy) or 110(yyyy) = mm-dd-yy

-- 11(yy) or 111(yyyy) = yy/mm/dd

-- 12(yy) or 112(yyyy) = yymmdd

-- 13 or 113(yyyy) = dd mon yyyy hh:mi:ss:mmm (24h)

-- 14(yy) or 114(yyyy) = hh:mi:ss:mmm (24h)

-- 20 or 120(yyyy) = yyyy-mm-dd hh:mi:ss (24h)

-- 21 or 121(yyyy) = yyyy-mm-dd hh:mi:ss.mmm (24h)

-- 126(yyyy) = yyyy-mm-ddThh:mi:ss.mmm (no spaces)

--130(yyyy) = dd mon yyyy hh:mi:ss:mmmAM

--131(yyyy) = dd/mm/yy hh:mi:ss:mmmAM