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

4 comments:

rudy said...

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

Anonymous said...

Ah, dit is handig

Bedankt

Daniel Stevenson said...

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

Thanks

Anonymous said...

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