Wednesday, September 21, 2005

Date formatting in SQL Server

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.

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)'
at

4 comments:

  1. Anonymous6:55 AM

    here's another version, can't remember where i found it --

    declare @dt as datetime
    declare @i as tinyint
    set @dt = getdate()
    set @i = 0
    print 'Code|' + space(36) + 'Code|'
    while @i < 22 begin
    print convert(char(3),@i) + ' | ' + convert(char(35),@dt,@i)
    + convert(char(3),(100+@i)) + ' | ' + convert(char(35),@dt,(100+@i))
    set @i = @i+1
    if (@i = 15) set @i = 20
    end

    ReplyDelete
  2. Anonymous6:02 AM

    Ah, dit is handig

    Bedankt

    ReplyDelete
  3. Anonymous5:48 AM

    For all us beginners out here, could you please explain where to insert the code in the data flow.

    Thanks

    ReplyDelete
  4. Anonymous6:12 AM

    Nice idea. I have always kept a copy of the table from BOL on the wall next to me (along with a copy of the table of values for datepart and all that.)

    Your format strings use the two digit yy year, but all the examples used the 100-series that uses a four digit year. It would be good to include the format values below 100, and to get all the format explanations correct.

    Roy Harvey
    Beacon Falls, CT

    ReplyDelete