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:
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
Ah, dit is handig
Bedankt
For all us beginners out here, could you please explain where to insert the code in the data flow.
Thanks
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
Post a Comment