Click here to monitor SSC

Simple-Talk columnist

Avoiding the TSQL ‘Convert’ styles.

Published 26 March 2006 3:10 pm

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(),’the dy day, the ww week of the 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’)

*/

11 Responses to “Avoiding the TSQL ‘Convert’ styles.”

  1. Anonymous says:

    I love this! I always seem to misplace my list of date format codes when I need them and have to waste time searching the internet for them. This is highly flexible and works great. Thank you very much for simplifying something that should have been so simple to start with.

  2. Phil Factor says:

    Thanks for that Chris. I appreciate the comment very much. Often I write things and have no idea if anyone subsequently finds them useful.
    It was one of those things that was quick to write and took ages to test!

  3. Anonymous says:

    Very useful and easy to modify, thanks for posting this.

    I’ve always been annoyed that T-SQL doesn’t have a more robust date format output.

  4. Phil Factor says:

    I forgot to mention that you gan get out a german, french or any other language of date format by using the….
    SET Language xxx
    … assignment

    Here isa a little test harness that shows a particular date in all the built-in languages within SQL Server

    set nocount on
    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

  5. Anonymous says:

    Very Helpfull and easy to use.. Thanks =)

  6. Anonymous says:

    Hello,

    I am storing a Column WeekStart as a string, in the format dd-mm-yyyy. I now need to extract the month, but when i use the following code :

    SELECT DATENAME(month, WeekEnd) AS [Month Name]
    FROM StaffHours

    It returns the days as months, it looks at DD and returns this as months, which is not what i want.

    Am i correct in assuming that firstly i need to CAST or CONVERT the date like so:

    SELECT CONVERT(CHAR(10), WeekStart, 103) + ‘ ‘ + LTRIM(RIGHT(CONVERT(CHAR(20),
    WeekStart, 22), 11))
    FROM StaffHours

    This works fine, but it returns: 01/02/2006 6
    Note the extra 6! Any ideas

    Now I would like to pass that as a parameter to the DateName function. Can someone give me some pointers please. Many thanks

    Neil
    n.khagram@herts.ac.uk

  7. Phil Factor says:

    Neil,
    No problem. This code will do the trick. I’ve just put a test string in but you would put your column name in ‘WeekStart’ instead and add the ‘FROM StaffHours’

    –if you want the name of the month
    SELECT DATENAME(month, convert(Datetime, ’24-2-2006′,105 )) AS [Month Name]
    –February

    –but if you want the number of the month
    SELECT DATEPART(month, convert(Datetime, ’24-2-2006′,105 )) AS [Month Name]
    –2

  8. Anonymous says:

    Excellent… very very nice. Thank You! I can’t believe that after all my searches for something like this I found it when I was looking to show someone some reasons why not to use CONVERT in queries… a very very nice suprise!

  9. Anonymous says:

    Hello friends,
    I posted above but now having some more problems

    I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!

    ALTER PROCEDURE SalaryBetweenDates
    (

    @WeekStart datetime,

    @WeekEnd datetime
    )
    AS

    BEGIN
    SET @WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@WeekStart ,103),’ ‘,’-'))
    SET @WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@WeekEnd ,103),’ ‘,’-'))
    END

    BEGIN
    SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
    sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)”Salary”
    From Staff As S INNER JOIN StaffHours As Sh
    On S.StaffNo = Sh.StaffNo
    WHERE sh.WeekStart >= (@WeekStart)
    AND sh.WeekEnd <= (@WeekEnd)

    FOR XML RAW (‘paySlip’), root(‘Staff’), ELEMENTS XSINIL
    END

    Return

  10. Anonymous says:

    I don’t know if this happened in translating this to HTML but there are some mistakes in your code. It took me 20 minutes to find them all and fix them but you are missing a couple of closing parentheses and towards the end you have dateename which should be DATENAME, a misspelled function.

    Great function though. Thanks.

  11. Phil Factor says:

    Oops. You were right. This was formatted with an early version of the ‘prettifier’ and it seems to have done a few creative changes which didn’t improve its ability to compile.  I’ve corrected the source above so nobody else hits this problem.
    I really must get around to rewriting this code. It is by far my most popular Blog entry and I never realised it would be when I dashed it off.

Leave a Reply