create function dbo.ufsFormat
(
@Date datetime,
@Format varchar(80)
)
returns nvarchar(80)
/*
RFC822 RFC822
Mmmths as 1–12 M
Mmmths as 01–12 Mm
Mmmths as Jan–Dec Mmm
Mmmths as January–December Mmmm
Mmmths as the first letter of the Mmmth Mmmmm
Days as 1–31 D
Days as 01–31 Dd
Days as Sun–Sat Ddd
Days as Sunday–Saturday Dddd
Years as 00–99 Yy
Years as 1900–9999 Yyyy
=
To display Use this code
Hours as 0–23 H
Hours as 00–23 Hh
Minutes as 0–59 M
Minutes as 00–59 Mm
Seconds as 0–59 S
Seconds as 00–59 Ss
Hours as 4 AM h AM/PM
Time as 4:36 PM h:mm AM/PM
Time as 4:36:03 P h:mm:ss A/P
Quarter of the year Qq
Day of the year Dy
Week of the year Ww
AM and PM If the format contains an AM or PM,
the hour is based on the 12-hour clock,
where \\"AM\\" or \\"PM\\"
Test Suite:
set nocount on
--just check that the date comes out correctly in different languages
declare @ii int, @iiMax int, @Name nvarchar(40)
declare @Languages table ( MyID int identity(1,1), Name nvarchar(40) )
insert into @Languages( Name ) select name from master..syslanguages
select @ii = min( MyID ), @iiMax = max( MyID ) from @Languages
while @ii <= @iiMax
begin
select @Name = name from @Languages where MyID = @ii
set language @Name
select @Name, dbo.ufsFormat( getDate(),'dddd dd mmmm yyyy hh:mm:ss:ms am/pm' )
select @ii = @ii + 1
end
set language us_english
select convert( varchar(50), null ) as Format, convert( varchar(50), null ) as Date where 1 = 0 union all
select 'mm/dd/yy', dbo.ufsFormat( getDate(), 'mm/dd/yy' ) union all
select 'mm/dd/yyyy', dbo.ufsFormat( getDate(), 'mm/dd/yyyy' ) union all
select 'yy.mm.dd', dbo.ufsFormat( getDate(), 'yy.mm.dd' ) union all
select 'dd/mm/yy', dbo.ufsFormat( getDate(), 'dd/mm/yy' ) union all
select 'dd.mm.yy', dbo.ufsFormat( getDate(), 'dd.mm.yy' ) union all
select 'dd-mm-yy', dbo.ufsFormat( getDate(), 'dd-mm-yy' ) union all
select 'dd Mmm yy', dbo.ufsFormat( getDate(), 'dd Mmm yy' ) union all
select 'Mmm dd, yy', dbo.ufsFormat( getDate(), 'Mmm dd, yy' ) union all
select 'yyyy.mm.dd', dbo.ufsFormat( getDate(), 'yyyy.mm.dd' ) union all
select 'hh:mm:ss', dbo.ufsFormat( getDate(), 'hh:mm:ss' ) union all
select 'dd/mm/yyyy', dbo.ufsFormat( getDate(), 'dd/mm/yyyy' ) union all
select 'dd.mm.yyyy', dbo.ufsFormat( getDate(), 'dd.mm.yyyy' ) union all
select 'dd-mm-yyyy', dbo.ufsFormat( getDate(), 'dd-mm-yyyy' ) union all
select 'dd Mmm yyyy', dbo.ufsFormat( getDate(), 'dd Mmm yyyy' ) union all
select 'Mmm dd, yyyy', dbo.ufsFormat( getDate(), 'Mmm dd, yyyy' ) union all
select 'Mmmm dd yyyy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'Mmmm dd yyyy hh:mm:ss:ms AM/PM' ) union all
select 'Mmm dd yyyy hh:mi:ss:mmm AM/PM', dbo.ufsFormat( getDate(), 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' ) union all
select 'Mmm dd yy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'Mmm dd yy hh:mm:ss:ms AM/PM' ) union all
select 'mm-dd-yy', dbo.ufsFormat( getDate(), 'mm-dd-yy' ) union all
select 'mm-dd-yyyy', dbo.ufsFormat( getDate(), 'mm-dd-yyyy' ) union all
select 'yy/mm/dd', dbo.ufsFormat( getDate(), 'yy/mm/dd' ) union all
select 'yyyy/mm/dd', dbo.ufsFormat( getDate(), 'yyyy/mm/dd' ) union all
select 'yymmdd', dbo.ufsFormat( getDate(), 'yymmdd' ) union all
select 'yyyymmdd', dbo.ufsFormat( getDate(), 'yyyymmdd' ) union all
select 'dd Mmm yy hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'dd Mmm yy hh:mm:ss:Ms' ) union all
select 'dd Mmm yyyy hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'dd Mmm yyyy hh:mm:ss:Ms' ) union all
select 'hh:mm:ss:Ms', dbo.ufsFormat( getDate(), 'hh:mm:ss:Ms' ) union all
select 'yyyy-mm-dd hh:mm:ss', dbo.ufsFormat( getDate(), 'yyyy-mm-dd hh:mm:ss' ) union all
select 'yyyy-mm-dd hh:mm:ss.Ms', dbo.ufsFormat( getDate(), 'yyyy-mm-dd hh:mm:ss.Ms' ) union all
select 'yyyy-mm-ddThh:mm:ss.Ms', dbo.ufsFormat( getDate(), 'yyyy-mm-ddThh:mm:ss.Ms' ) union all
select 'dd Mmm yyyy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'dd Mmm yyyy hh:mm:ss:ms AM/PM' ) union all
select 'dd/mm/yy hh:mm:ss:ms AM/PM', dbo.ufsFormat( getDate(), 'dd/mm/yy hh:mm:ss:ms AM/PM' ) union all
select 'RFC822', dbo.ufsFormat( getDate(), 'RFC822' ) union all
select 't\he dy \da\y, t\he ww week of t\he year', dbo.ufsFormat( getDate(), 't\he dy \da\y, t\he ww week of t\he year' ) union all
select 'dddd dd mmmm yyyy h am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h am/pm' ) union all
select 'dddd dd mmmm yyyy h:m:s am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h:m:s am/pm' ) union all
select 'dddd dd mmmm yyyy hh:mm:ss:ms am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy hh:mm:ss:ms am/pm' ) union all
select 'dddd dd mmmm yyyy h:m:s:ms am/pm', dbo.ufsFormat( getDate(), 'dddd dd mmmm yyyy h:m:s:ms am/pm' ) union all
select 'dd mmmmm yyyy', dbo.ufsFormat( getDate(), 'dd mmmmm yyyy' )
select 'yyyy.mm.dd.hh.mm.ss.ms', dbo.ufsFormat( getDate(), 'yyyy.mm.dd.hh.mm.ss.ms' )
*/
as
begin
declare @Dateformat int, @ReturnedDate varchar(80), @TwelveHourClock int, @Before int, @pos int, @Escape int
select
@ReturnedDate = 'error! unrecognised format ' + @Format,
@DateFormat =
case @Format
when 'mmm dd yyyy hh:mm AM/PM' then 100
when 'mm/dd/yy' then 1
when 'mm/dd/yyyy' then 101
when 'yy.mm.dd' then 2
when 'dd/mm/yy' then 3
when 'dd.mm.yy' then 4
when 'dd-mm-yy' then 5
when 'dd Mmm yy' then 6
when 'Mmm dd, yy' then 7
when 'hh:mm:ss' then 8
when 'yyyy.mm.dd' then 102
when 'dd/mm/yyyy' then 103
when 'dd.mm.yyyy' then 104
when 'dd-mm-yyyy' then 105
when 'dd Mmm yyyy' then 106
when 'Mmm dd, yyyy' then 107
when 'Mmm dd yyyy hh:mm:ss:ms AM/PM' then 9
when 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' then 9
when 'Mmm dd yy hh:mm:ss:ms AM/PM' then 109
when 'mm-dd-yy' then 10
when 'mm-dd-yyyy' then 110
when 'yy/mm/dd' then 11
when 'yyyy/mm/dd' then 111
when 'yymmdd' then 12
when 'yyyymmdd' then 112
when 'dd Mmm yyyy hh:mm:ss:Ms' then 113
when 'hh:mm:ss:Ms' then 14
when 'yyyy-mm-dd hh:mm:ss' then 120
when 'yyyy-mm-dd hh:mm:ss.Ms' then 121
when 'yyyy-mm-ddThh:mm:ss.Ms' then 126
when 'dd Mmm yyyy hh:mm:ss:ms AM/PM' then 130
when 'dd/mm/yy hh:mm:ss:ms AM/PM' then 131
when 'RFC822' then -2
when 'dd Mmm yyyy hh:mm' then -4
else -1
end
select @ReturnedDate = 'error! unrecognised format ' + @Format + convert( varchar, @DateFormat )
-- standard formats
if @DateFormat >= 0
return convert( varchar, @Date, @DateFormat )
-- RFC822 format
if @DateFormat = -2
return left( datename( dw, @Date ), 3 ) + ', ' + stuff( convert( nvarchar, @Date, 113 ), 21, 4,' GMT' )
-- European day format with minutes
if @DateFormat = -4
return convert( char(17), @Date, 113 )
select @Before = len( @Format )
select @Format = replace( replace( replace( @Format,'AM/PM', '#' ), 'AM', '#' ), 'PM', '#' )
select @TwelveHourClock =
case
when @Before > len( @Format )
then 109
else 113
end,
@ReturnedDate = ''
while (1=1)
begin
select @pos = patindex( '%[yqmidwhs:#]%', @Format + ' ' )
if @pos = 0 -- no more date format strings
begin
select @ReturnedDate = @ReturnedDate + @Format
break
end
if @pos > 1 -- some stuff to pass through first
begin
select @escape = charindex( '\', @Format + '\' )
--is it a literal character that is escaped?
if @escape < @pos
begin
select @ReturnedDate = @ReturnedDate + substring( @Format, 1, @escape - 1 ) + substring( @Format, @escape + 1, 1 )
select @Format = rtrim( substring( @Format, @Escape + 2, 80 ) )
continue
end
select @ReturnedDate = @ReturnedDate + substring( @Format, 1, @pos - 1 )
select @Format = rtrim( substring( @Format, @pos, 80 ) )
end
select @pos = patindex( '%[^yqmidwhs:#]%', @Format + ' ' ) --get the end
select @ReturnedDate = @ReturnedDate +
case substring( @Format, 1, @pos - 1 )
when 'M' then convert( varchar(2), datepart( month, @Date ) ) --Mmmths as 1–12
when 'Mm' then convert( char(2), @Date, 101) --Mmmths as 01–12
when 'Mmm' then convert( char(3), datename( month, @Date ) ) --Mmmths as Jan–Dec
when 'Mmmm' then datename( month, @Date ) --Mmmths as January–December
when 'Mmmmm' then convert( char(1), datename( month, @Date ) ) --Mmmths as the first letter of the Mmmth
when 'D' then convert( varchar(2), datepart( day, @Date ) ) --Days as 1–31
when 'Dd' then convert( char(2), @date, 103 ) --Days as 01–31
when 'Ddd' then convert( char(3), datename( weekday, @Date ) ) --Days as Sun–Sat
when 'Dddd' then datename( weekday, @Date ) --Days as Sunday–Saturday
when 'Yy' then convert( char(2), @Date, 12 ) --Years as 00–99
when 'Yyyy' then datename( year, @Date ) --Years as 1900–9999
when 'hh:mm:ss' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 8 )
when 'hh:mm:ss:ms' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 )
when 'h:mm:ss' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 8 )
when 'hh:mi:ss:mmm' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 ) --the SQL Server BOL syntax, for compatibility
when 'h:mm:ss:ms' then substring( convert( char(30), @date, @TwelveHourClock ), 13, 12 )
when 'H:m:s' then substring( replace( ':' + substring( convert( char(30), @Date, @TwelveHourClock ), 13, 8 ), ':0', ':' ), 2, 30 )
when 'H:m:s:ms' then substring( replace( ':' + substring( convert( char(30), @Date, @TwelveHourClock ), 13, 12 ), ':0', ':' ), 2, 30 )
when 'hh' then replace( substring( convert( char(30), @Date, @TwelveHourClock ), 13, 2 ), ' ', '0' ) --Hours as 00–23
when 'h' then ltrim( substring( convert( char(30), @Date, @TwelveHourClock ), 13, 2 ) ) --Hours as 0–23
when 'Mi' then datename( minute, @date ) --Minutes as 00–59
when 'mn' then datename( minute, @date )
when 'm' then convert( varchar(2), datepart( minute, @date ) )
when 'ss' then datename( second,@date) --Seconds as 0–59
when 'S' then convert( varchar(2), datepart( second, @date ) ) --Seconds as 0–59
when 'ms' then datename( millisecond, @date ) --AM/PM
when 'mmm' then datename( millisecond, @date )
when 'dy' then datename( dy, @date )
when 'qq' then datename( qq, @date )
when 'ww' then datename( ww, @date )
when '#' then reverse( substring( reverse( convert( char(26), @date, 109 ) ), 1, 2 ) )
else substring( @Format, 1, @pos - 1 )
end
select @Format = rtrim( substring( @Format, @pos, 80 ) )
end
return @ReturnedDate
end
This entry was posted
on Wednesday, June 10, 2009
and is filed under
SQL Server
.
You can leave a response
and follow any responses to this entry through the
Subscribe to:
Post Comments (Atom)
.
Filed under
SQL Server
?l'ta?m
0 comments