Аналог функции FormatDateTime на TSQL
01.01.2007
CREATE FUNCTION dbo.FormatDateTime(@Format varchar(1000), @Time datetime)
RETURNS varchar(1000) AS
/*©Drkb v.3(2007): www.drkb.ru,
®Vit (Vitaly Nevzorov) - nevzorov@yahoo.com*/
BEGIN
Declare @temp varchar(20)
/*Special substitutions to avoid formating prepared strings*/
Declare @dddd varchar(35) Set @dddd='QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ'
Declare @ddd varchar(35) Set @ddd= 'WWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW'
Declare @mmmm varchar(35) Set @mmmm='EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE'
Declare @mmm varchar(35) Set @mmm= 'RRRRRRRRRRRRRRRRRRRRRRRRRRRRRRR'
Declare @am varchar(35) Set @am= 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
Declare @pm varchar(35) Set @pm= 'PPPPPPPPPPPPPPPPPPPPPPPPPPPPPPP'
if PATINDEX('%dddd%' , @Format)>0 Set @Format=Replace(@Format,'dddd', @dddd)
if PATINDEX('%ddd%' , @Format)>0 Set @Format= Replace(@Format,'ddd', @ddd)
if PATINDEX('%mmmm%' , @Format)>0 Set @Format=Replace(@Format,'mmmm',@mmmm)
if PATINDEX('%mmm%' , @Format)>0 Set @Format=Replace(@Format,'mmm', @mmm)
if PATINDEX('%doy%' , @Format)>0
begin
Declare @Doy int
Set @Doy=Case Month(@Time)
When 1 Then 0
When 2 Then 31 -- Jan
Else
Case
When Year(@Time)%4=0 and Year(@Time)%400<>0 Then 31+29
Else 31+28
End -- Feb
End
Set @Doy=Case Month(@Time)
When 4 Then @Doy+31 -- Mar
When 5 Then @Doy+31+30 -- Apr
When 6 Then @Doy+31+30+31-- May
When 7 Then @Doy+31+30+31+30-- Jun
When 8 Then @Doy+31+30+31+30+31-- Jul
When 9 Then @Doy+31+30+31+30+31+31-- Aug
When 10 Then @Doy+31+30+31+30+31+31+30-- Sep
When 11 Then @Doy+31+30+31+30+31+31+30+31-- Oct
When 12 Then @Doy+31+30+31+30+31+31+30+31+30-- Nov
Else @Doy
End
Set @Doy=@Doy+Day(@Time)
Set @Format= Case
When @Doy<10 Then Replace(@Format,'doy', '00'+cast(@Doy as varchar(1)))
When @Doy>=100 Then Replace(@Format,'doy', cast(@Doy as varchar(3)))
Else Replace(@Format,'doy', '0'+cast(@Doy as varchar(2)))
End
end
if PATINDEX('%dd%' , @Format)>0
begin
if DATENAME(d, @time)<10
Set @Format= Replace(@Format,'dd', '0'+DATENAME(d, @time))
else
Set @Format= Replace(@Format,'dd', DATENAME(d, @time))
end
if PATINDEX('%d%' , @Format)>0 Set @Format= Replace(@Format,'d', DATENAME(d, @time))
if PATINDEX('%yyyy%' , @Format)>0 Set @Format= Replace(@Format,'yyyy', Year(@Time))
if PATINDEX('%yy%' , @Format)>0 Set @Format= Replace(@Format,'yy', Right(Cast(Year(@Time) as varchar(4)),2))
if PATINDEX('%hh%' , @Format)>0
begin
if PATINDEX('%am/pm%' , @Format)>0
begin
Set @Format=
Case DATENAME(hh, @time)
When 0 Then Replace(@Format,'hh', '12')
When 1 Then Replace(@Format,'hh', '01')
When 2 Then Replace(@Format,'hh', '02')
When 3 Then Replace(@Format,'hh', '03')
When 4 Then Replace(@Format,'hh', '04')
When 5 Then Replace(@Format,'hh', '05')
When 6 Then Replace(@Format,'hh', '06')
When 7 Then Replace(@Format,'hh', '07')
When 8 Then Replace(@Format,'hh', '08')
When 9 Then Replace(@Format,'hh', '09')
When 10 Then Replace(@Format,'hh', '10')
When 11 Then Replace(@Format,'hh', '11')
When 12 Then Replace(@Format,'hh', '12')
When 13 Then Replace(@Format,'hh', '01')
When 14 Then Replace(@Format,'hh', '02')
When 15 Then Replace(@Format,'hh', '03')
When 16 Then Replace(@Format,'hh', '04')
When 17 Then Replace(@Format,'hh', '05')
When 18 Then Replace(@Format,'hh', '06')
When 19 Then Replace(@Format,'hh', '07')
When 20 Then Replace(@Format,'hh', '08')
When 21 Then Replace(@Format,'hh', '09')
When 22 Then Replace(@Format,'hh', '10')
When 23 Then Replace(@Format,'hh', '11')
When 24 Then Replace(@Format,'hh', '12')
End
Set @Format=
Case
When DATENAME(hh, @time)<12 Then Replace(@Format,'am/pm', @am)
Else Replace(@Format,'am/pm', @pm)
End
end
else
begin
if DATENAME(hh, @time)<10
Set @Format= Replace(@Format,'hh', '0'+cast(DATENAME(hh, @time) as varchar(2)))
else
Set @Format= Replace(@Format,'hh', DATENAME(hh, @time))
end
end
if PATINDEX('%h%' , @Format)>0
begin
if PATINDEX('%am/pm%' , @Format)>0
begin
Set @Format=
Case DATENAME(hh, @time)
When 0 Then Replace(@Format,'hh', '12')
When 1 Then Replace(@Format,'hh', '1')
When 2 Then Replace(@Format,'hh', '2')
When 3 Then Replace(@Format,'hh', '3')
When 4 Then Replace(@Format,'hh', '4')
When 5 Then Replace(@Format,'hh', '5')
When 6 Then Replace(@Format,'hh', '6')
When 7 Then Replace(@Format,'hh', '7')
When 8 Then Replace(@Format,'hh', '8')
When 9 Then Replace(@Format,'hh', '9')
When 10 Then Replace(@Format,'hh', '10')
When 11 Then Replace(@Format,'hh', '11')
When 12 Then Replace(@Format,'hh', '12')
When 13 Then Replace(@Format,'hh', '1')
When 14 Then Replace(@Format,'hh', '2')
When 15 Then Replace(@Format,'hh', '3')
When 16 Then Replace(@Format,'hh', '4')
When 17 Then Replace(@Format,'hh', '5')
When 18 Then Replace(@Format,'hh', '6')
When 19 Then Replace(@Format,'hh', '7')
When 20 Then Replace(@Format,'hh', '8')
When 21 Then Replace(@Format,'hh', '9')
When 22 Then Replace(@Format,'hh', '10')
When 23 Then Replace(@Format,'hh', '11')
When 24 Then Replace(@Format,'hh', '12')
End
Set @Format=
Case
When DATENAME(hh, @time)<12 Then Replace(@Format,'am/pm', @am)
Else Replace(@Format,'am/pm', @pm)
End
end
else
begin
Set @Format= Replace(@Format,'h', DATENAME(hh, @time))
end
end
if PATINDEX('%mm%' , @Format)>0
begin
if Month(@Time)<10
Set @Format= Replace(@Format,'mm', '0'+cast(Month(@Time) as varchar(2)))
else
Set @Format= Replace(@Format,'mm', Month(@Time))
end
if PATINDEX('%m%' , @Format)>0 Set @Format= Replace(@Format,'m', Month(@Time))
if PATINDEX('%nn%' , @Format)>0
begin
if DATENAME(mi, @time)<10
Set @Format= Replace(@Format,'nn', '0'+cast(DATENAME(mi, @time) as varchar(2)))
else
Set @Format= Replace(@Format,'nn', DATENAME(mi, @time))
end
if PATINDEX('%n%' , @Format)>0 Set @Format= Replace(@Format,'n', DATENAME(mi, @time))
if PATINDEX('%ss%' , @Format)>0
begin
if DATENAME(ss, @time)<10
Set @Format= Replace(@Format,'ss', '0'+cast(DATENAME(ss, @time) as varchar(2)))
else
Set @Format= Replace(@Format,'ss', DATENAME(ss, @time))
end
if PATINDEX('%s%' , @Format)>0 Set @Format= Replace(@Format,'s', DATENAME(ss, @time))
if PATINDEX('%'+@dddd+'%' , @Format)>0
begin
Set @Format=
Case DAtepart(weekday, @time)
When 1 Then Replace(@Format,@dddd, 'Sunday')
When 2 Then Replace(@Format,@dddd, 'Monday')
When 3 Then Replace(@Format,@dddd, 'Tuesday')
When 4 Then Replace(@Format,@dddd, 'Wednesday')
When 5 Then Replace(@Format,@dddd, 'Thursday')
When 6 Then Replace(@Format,@dddd, 'Friday')
When 7 Then Replace(@Format,@dddd, 'Saturday')
End
end
if PATINDEX('%'+@ddd+'%' , @Format)>0
begin
Set @Format=
Case DAtepart(weekday, @time)
When 1 Then Replace(@Format,@ddd, 'Sun')
When 2 Then Replace(@Format,@ddd, 'Mon')
When 3 Then Replace(@Format,@ddd, 'Tue')
When 4 Then Replace(@Format,@ddd, 'Wed')
When 5 Then Replace(@Format,@ddd, 'Thu')
When 6 Then Replace(@Format,@ddd, 'Fri')
When 7 Then Replace(@Format,@ddd, 'Sat')
End
end
if PATINDEX('%'+@mmmm+'%' , @Format)>0
begin
Set @Format=
Case DAtepart(month, @time)
When 1 Then Replace(@Format,@mmmm, 'January')
When 2 Then Replace(@Format,@mmmm, 'February')
When 3 Then Replace(@Format,@mmmm, 'March')
When 4 Then Replace(@Format,@mmmm, 'April')
When 5 Then Replace(@Format,@mmmm, 'May')
When 6 Then Replace(@Format,@mmmm, 'June')
When 7 Then Replace(@Format,@mmmm, 'July')
When 8 Then Replace(@Format,@mmmm, 'August')
When 9 Then Replace(@Format,@mmmm, 'September')
When 10 Then Replace(@Format,@mmmm, 'October')
When 11 Then Replace(@Format,@mmmm, 'November')
When 12 Then Replace(@Format,@mmmm, 'December')
End
end
if PATINDEX('%'+@mmm+'%' , @Format)>0
begin
Set @Format=
Case DAtepart(month, @time)
When 1 Then Replace(@Format,@mmm, 'Jan')
When 2 Then Replace(@Format,@mmm, 'Feb')
When 3 Then Replace(@Format,@mmm, 'Mar')
When 4 Then Replace(@Format,@mmm, 'Apr')
When 5 Then Replace(@Format,@mmm, 'May')
When 6 Then Replace(@Format,@mmm, 'Jun')
When 7 Then Replace(@Format,@mmm, 'Jul')
When 8 Then Replace(@Format,@mmm, 'Aug')
When 9 Then Replace(@Format,@mmm, 'Sep')
When 10 Then Replace(@Format,@mmm, 'Oct')
When 11 Then Replace(@Format,@mmm, 'Nov')
When 12 Then Replace(@Format,@mmm, 'Dec')
End
end
if PATINDEX('%'+@am+'%' , @Format)>0 Set @Format=Replace(@Format, @am,'AM')
if PATINDEX('%'+@pm+'%' , @Format)>0 Set @Format=Replace(@Format, @pm,'PM')
Return @Format
Поддерживает любые маски стандартных форматов, писал по спецификации:
Цитата
d Displays the day as a number without a leading zero (1-31).
dd Displays the day as a number with a leading zero (01-31).
ddd Displays the day as an abbreviation (Sun-Sat)
dddd Displays the day as a full name (Sunday-Saturday)
m Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mm Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.
mmm Displays the month as an abbreviation (Jan-Dec)
mmmm Displays the month as a full name (January-December)
yy Displays the year as a two-digit number (00-99).
yyyy Displays the year as a four-digit number (0000-9999).
h Displays the hour without a leading zero (0-23).
hh Displays the hour with a leading zero (00-23).
n Displays the minute without a leading zero (0-59).
nn Displays the minute with a leading zero (00-59).
s Displays the second without a leading zero (0-59).
ss Displays the second with a leading zero (00-59).
am/pm Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.
Пример использования:
Declare @MyDate datetime
Set @MyDate=GetDate()
Select
dbo.FormatDateTime('dd/mm/yyyy', @MyDate),
dbo.FormatDateTime('dd mmm yy', @MyDate),
dbo.FormatDateTime('mmmm''dd yyyy hh:nn', @MyDate)
Результат будет типа:
27/07/2006
27 Jul 06
July'27 2006 14:21
Любой неописанный мусор в маске останется где он был, функция никаких ошибок не генерит, интерпретируются только описанные форматы:
Declare @MyDate datetime
Set @MyDate=GetDate()
Select
dbo.FormatDateTime('Сегодня у нас yyyy-mm-dd (dddd), чёрт бы его побрал!', @MyDate)
Результат будет типа:
Сегодня у нас 2006-07-27 (Thursday), чёрт бы его побрал!
Несмотря на громоздкость функция работает достаточно быстро.