I got this script a long time ago, and I do not remember from where. But I run it every time that I have to format a date or time. It shows you what format value to use in the CONVERT statement.
/*******************************************************************************************
sp_datetime_formats
Written By: Doug Deneau, FinServ Technology Services, PLC (Fettan)
Company Desc: Payment services technology company in Ethoipia
Date Written: March, 2009
Website: www.GoFettan.com
This stored procedure is used to generate all datetime formats when using the
CONVERT(varchar(30),@MyDate,<style>) function. It shows sample formats of each
variation in style parameter. The output also shows samples for the various
date arguments within the date functions.
INPUT PARAMTERS
---------------
<none>
OUTPUT DATA
-----------
WO_Century - Style argument value without the century in the year (2-digit year)
WO_Cent_No_Offset - Sample output without the century in the year and no offset
WO_Cent_With_Offset - Sample output without the century in the year and offset
W_Century - Style argument value with the century in the year (4-digit year)
W_Cent_No_Offset - Sample output with the century in the year and no offset
W_Cent_With_Offset - Sample output with the century in the year and offset
DateName_Parm - Date Part argument name for use in various date functions
DateName_Example - Sample data from using the corresponding Date Part argument
MODIFICATION LOG
----------------
<name> - <datetime> - <description of change>
********************************************************************************************/
set nocount on
declare @Fmt int,
@More char(1),
@Today datetime,
@TodayO datetimeoffset
select @Today = GETDATE()
select @TodayO = SYSDATETIMEOFFSET()
create table #data
(WO_Century varchar(5) null,
WO_Cent_No_Offset varchar(50) null,
WO_Cent_With_Offset varchar(50) null,
W_Century int not null,
W_Cent_No_Offset varchar(50) null,
W_Cent_With_Offset varchar(50) null,
DateName_Parm varchar(50) null,
DateName_Example varchar(50) null)
select @More = 'Y', @Fmt = 0
while @More = 'Y' begin
insert into #data (WO_Century, WO_Cent_No_Offset, WO_Cent_With_Offset, W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select @Fmt, Convert(varchar(50),@Today,@Fmt), Convert(varchar(50),@TodayO,@Fmt),
@Fmt + 100, Convert(varchar(50),@Today,@Fmt + 100), Convert(varchar(50),@TodayO,@Fmt + 100)
select @Fmt = @Fmt + 1
if @Fmt > 14 select @More = 'N'
end -- of loop
insert into #data (WO_Century, WO_Cent_No_Offset, WO_Cent_With_Offset, W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 20, Convert(varchar(50),@Today,20), Convert(varchar(50),@TodayO,20), 120, Convert(varchar(50),@Today,120), Convert(varchar(50),@TodayO,120)
insert into #data (WO_Century, WO_Cent_No_Offset, WO_Cent_With_Offset, W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 21, Convert(varchar(50),@Today,21), Convert(varchar(50),@TodayO,21), 121, Convert(varchar(50),@Today,121), Convert(varchar(50),@TodayO,121)
insert into #data (W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 126, Convert(varchar(50),@Today,126), Convert(varchar(50),@TodayO,126)
insert into #data (W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 127, Convert(varchar(50),@Today,127), Convert(varchar(50),@TodayO,127)
insert into #data (W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 130, Convert(varchar(50),@Today,130), Convert(varchar(50),@TodayO,130)
insert into #data (W_Century, W_Cent_No_Offset, W_Cent_With_Offset)
select 131, Convert(varchar(50),@Today,131), Convert(varchar(50),@TodayO,131)
update #data set WO_Century = '<def>' where WO_Century = '0'
-- datepart formats
update #data set DateName_Parm = 'year,yyyy,yy', DateName_Example = DATENAME(year,@Today) where WO_Century = '0'
update #data set DateName_Parm = 'quarter,qq,q', DateName_Example = DATENAME(quarter,@Today) where WO_Century = '1'
update #data set DateName_Parm = 'month,mm,m', DateName_Example = DATENAME(month,@Today) where WO_Century = '2'
update #data set DateName_Parm = 'dayofyear,dy,y', DateName_Example = DATENAME(dayofyear,@Today) where WO_Century = '3'
update #data set DateName_Parm = 'day,dd,d', DateName_Example = DATENAME(day,@Today) where WO_Century = '4'
update #data set DateName_Parm = 'week,wk,ww', DateName_Example = DATENAME(week,@Today) where WO_Century = '5'
update #data set DateName_Parm = 'weekday,dw', DateName_Example = DATENAME(weekday,@Today) where WO_Century = '6'
update #data set DateName_Parm = 'hour,hh', DateName_Example = DATENAME(hour,@Today) where WO_Century = '7'
update #data set DateName_Parm = 'minute,mi,mn', DateName_Example = DATENAME(minute,@Today) where WO_Century = '8'
update #data set DateName_Parm = 'second,ss,s', DateName_Example = DATENAME(second,@Today) where WO_Century = '9'
update #data set DateName_Parm = 'millisecond,ms', DateName_Example = DATENAME(millisecond,@Today) where WO_Century = '10'
update #data set DateName_Parm = 'microsecond,mcs', DateName_Example = DATENAME(microsecond,@Today) where WO_Century = '11'
update #data set DateName_Parm = 'nanosecond,ns', DateName_Example = DATENAME(nanosecond,@Today) where WO_Century = '12'
update #data set DateName_Parm = 'TZoffset,tz', DateName_Example = DATENAME(TZOFFSET,@TodayO) where WO_Century = '13'
select * from #data
drop table #data
go
