A recent blog was extolling the advantages of being able to use the CLR routines in SQL Server 2005 to do Microsoft-style date formats, instead of having the fuss and bother of remembering the CAST or CONVERT styles for the various representations of dates
It is actually quite easy to do these date formats in SQL 2000 as well, with a function. The advantage of using a function is that you can add your own favourite date formats in to taste. If your programmers are PHP programmers rather than 'softies, you can even change it for the PHP date formatting convention.
Here is the straight-forward function
I was going to add a few esoteric bits and pieces such as the obvious one of being able to add the 'st', 'nd', 'rd', and 'th' suffix (as in '21st March 2006') bit the function was beginning to get a bit long for a blog. The reason the function was getting long was that I use the built-in date formats wherever possible before iterating through the format string only to do a 'special'.
There is more optimisation to be had, possibly at the expense of the clarity of the code.I'd already written it when I realised that there should be just one loop, with the standards peeling off at the first iteration. It maybe should be done in a table rather than using all those case statements but I doubt that one gains a great deal in performance. I'd be interested to hear from anyone who spots a bug or can suggest an improvement. I'd love to do an extention that represented the time the way one speaks it in english, with 'Ten to one' or 'quarter to four', 'half past six' and so on, but that will have to wait for another occasion.
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&nb*/
ALTER FUNCTION [dbo].[ufsFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT
SELECT @ReturnedDate='error! unrecognised format '+@format
SELECT @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(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
--check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2--then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ', '
+ STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT')
ELSE IF @DateFormat=-4--then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
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)--forever
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
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1)
+SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end
SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+
CASE SUBSTRING(@Format,1,@pos-1)
--Mmmths as 1–12
WHEN 'M' THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
--Mmmths as 01–12
WHEN 'Mm' THEN CONVERT(CHAR(2),@Date,101)
--Mmmths as Jan–Dec
WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
--Mmmths as January–December
WHEN 'Mmmm' THEN DATENAME(MONTH,@Date)
--Mmmths as the first letter of the Mmmth
WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
--Days as 1–31
WHEN 'D' THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
--Days as 01–31
WHEN 'Dd' THEN CONVERT(CHAR(2),@date,103)
--Days as Sun–Sat
WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
--Days as Sunday–Saturday
WHEN 'Dddd' THEN DATENAME(weekday,@Date)
--Years as 00–99
WHEN 'Yy' THEN CONVERT(CHAR(2),@Date,12)
--Years as 1900–9999
WHEN 'Yyyy' THEN DATENAME(YEAR,@Date)
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)
--the SQL Server BOL syntax, for compatibility
WHEN 'hh:mi:ss:mmm' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
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)
--Hours as 00–23
WHEN 'hh' THEN REPLACE(SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,2),' ','0')
--Hours as 0–23
WHEN 'h' THEN LTRIM(SUBSTRING(CONVERT(CHAR(30),
@Date,@TwelveHourClock),13,2))
--Minutes as 00–59
WHEN 'Mi' THEN DATENAME(minute,@date)
WHEN 'mm' THEN DATENAME(minute,@date)
WHEN 'm' THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
--Seconds as 0–59
WHEN 'ss' THEN DATENAME(second,@date)
--Seconds as 0–59
WHEN 'S' THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
--AM/PM
WHEN 'ms' THEN DATENAME(millisecond,@date)
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
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
END
RETURN @ReturnedDate
END
/*
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 dbo.ufsFormat(getDate(),'dddd dd mmmm yyyy hh:mm:ss:ms am/pm')
Select @ii=@ii+1
end
Set language us_english
Select dbo.ufsFormat(getDate(),'mm/dd/yy')
Select dbo.ufsFormat(getDate(),'mm/dd/yyyy')
Select dbo.ufsFormat(getDate(),'yy.mm.dd')
Select dbo.ufsFormat(getDate(),'dd/mm/yy')
Select dbo.ufsFormat(getDate(),'dd.mm.yy')
Select dbo.ufsFormat(getDate(),'dd-mm-yy')
Select dbo.ufsFormat(getDate(),'dd Mmm yy')
Select dbo.ufsFormat(getDate(),'Mmm dd, yy')
Select dbo.ufsFormat(getDate(),'yyyy.mm.dd')
Select dbo.ufsFormat(getDate(),'hh:mm:ss')
Select dbo.ufsFormat(getDate(),'dd/mm/yyyy')
Select dbo.ufsFormat(getDate(),'dd.mm.yyyy')
Select dbo.ufsFormat(getDate(),'dd-mm-yyyy')
Select dbo.ufsFormat(getDate(),'dd Mmm yyyy')
Select dbo.ufsFormat(getDate(),'Mmm dd, yyyy')
Select dbo.ufsFormat(getDate(),'Mmmm dd yyyy hh:mm:ss:ms AM/PM')
Select dbo.ufsFormat(getDate(),'Mmm dd yyyy hh:mi:ss:mmm AM/PM')
Select dbo.ufsFormat(getDate(),'Mmm dd yy hh:mm:ss:ms AM/PM')
Select dbo.ufsFormat(getDate(),'mm-dd-yy')
Select dbo.ufsFormat(getDate(),'mm-dd-yyyy')
Select dbo.ufsFormat(getDate(),'yy/mm/dd')
Select dbo.ufsFormat(getDate(),'yyyy/mm/dd')
Select dbo.ufsFormat(getDate(),'yymmdd')
Select dbo.ufsFormat(getDate(),'yyyymmdd')
Select dbo.ufsFormat(getDate(),'dd Mmm yy hh:mm:ss:Ms')
Select dbo.ufsFormat(getDate(),'dd Mmm yyyy hh:mm:ss:Ms')
Select dbo.ufsFormat(getDate(),'hh:mm:ss:Ms')
Select dbo.ufsFormat(getDate(),'yyyy-mm-dd hh:mm:ss')
Select dbo.ufsFormat(getDate(),'yyyy-mm-dd hh:mm:ss.Ms')
Select dbo.ufsFormat(getDate(),'yyyy-mm-ddThh:mm:ss.Ms')
Select dbo.ufsFormat(getDate(),'dd Mmm yyyy hh:mm:ss:ms AM/PM')
Select dbo.ufsFormat(getDate(),'dd/mm/yy hh:mm:ss:ms AM/PM')
Select dbo.ufsFormat(getDate(),'RFC822')
Select dbo.ufsFormat(getDate(),'t\he dy \da\y, t\he ww week of t\he year')
Select dbo.ufsFormat(getDate(),'dddd dd mmmm yyyy h am/pm')
Select dbo.ufsFormat(getDate(),'dddd dd mmmm yyyy h:m:s am/pm')
Select dbo.ufsFormat(getDate(),'dddd dd mmmm yyyy hh:mm:ss:ms am/pm')
Select dbo.ufsFormat(getDate(),'dddd dd mmmm yyyy h:m:s:ms am/pm')
Select dbo.ufsFormat(getDate(),'dd mmmmm yyyy')
*/