One of the comments that often comes up in the Introduction to SQL course, is that Oracle provides much more in the way of formatting dates than SQL Server. I’m aware that many people are of the view, “formatting should be done in the client application, it’s not the job of the database”. I agree with that to a certain extent – good practice will often dictate that formatting should be done outside of the database – but there are times when I get frustrated at the limitations of SQL Server in this area.
For one thing, SQL Server provides the ability to send emails from within the database. I would have thought that this alone justifies better formatting than is currently available.
Secondly, we don’t live in a perfect world where we have the choice to put all code in the correct layer every time. We might only have control of the data tier, with a presentation tier displaying everything returned from a stored procedure, and we may not be able to change the code in that presentation tier.
And finally, if date formatting should never be done in the database, why bother providing the CONVERT function at all? To provide it, but with only a very limited set of formats, seems inconsistent.
So one of the first thing I did when I came to SQL Server from Oracle was to code up a quick and simple date formatting routine, that I wanted to work in a similar way to Oracle’s TO_CHAR function for dates. I only included the most basic format support and expected to have to extend it regularly as my requirements got more complex, but in fact I don’t think I’ve done much in the way if altering it, it pretty much has met my requirements whenever I’ve needed it. I still use CONVERT when one of the Microsoft supplied date formats is acceptable, but this allows me a bit more flexibility. Being a user defined function, performance will be affected, so I use it sparingly, but it definitely comes in handy. It’s designed to accept a date, and then an Oracle-style format, like ‘DD MON YY HH:MI’, and it returns the input date in the specified format, as a string. Alternatively I allow the passing of special strings like ‘Standard Date’, ‘Time only’ etc, which can be customised for the requirements of a particular project.
As it was almost the first UDF I wrote, it doesn’t use best coding practices, and I can now see several improvements I could make, but for the few times I need it, this works fine for me. If it’s useful for you, feel free to copy it, and maybe make some of those long awaited improvements I never got round to making.
Dean
=====================================================================================
-- Author: D Bullen
-- Description: This procedure accepts a date value, formats it, and returns it as a string.
-- The format is passed in as the @Format parameter, and it accepts the following:
-- DD - Date
-- MM - Month number
-- MNTH - Month Name
-- MON - Month Short Name
-- YYYY - 4 digit year
-- YY - 2 digit year
-- HH - Hours
-- MI - Minutes
--
-- Alternatively prefined standard formats can be passed in and translated into basic
-- format types, e.g. 'Standard Date & Time' - translated to 'DD MNTH YYYY at HH:MI'
-- =====================================================================================
CREATE FUNCTION [General].[FormattedDate]
( @DateIn DATETIME
,@Format VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @FormattedDate VARCHAR(100)
IF @Format = 'Standard Date' SET @Format = 'DD MNTH YYYY'
IF @Format = 'Standard Date & Time' SET @Format = 'DD MNTH YYYY at HH:MI'
IF @Format = 'Short Date' SET @Format = 'DD MON YY'
IF @Format = 'Short Date & Time' SET @Format = 'DD MON YY HH:MI'
IF @Format = 'Time Only' SET @Format = 'HH:MI'
DECLARE @DateNum VARCHAR(2)
SET @DateNum = CASE WHEN LEN(CAST(DATEPART(day, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(day, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(day, @DateIn) AS VARCHAR) END
DECLARE @MonthNum VARCHAR(2)
SET @MonthNum = CASE WHEN LEN(CAST(DATEPART(month, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(month, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(month, @DateIn) AS VARCHAR) END
DECLARE @MonthName VARCHAR(20)
SET @MonthName = DATENAME(month, @DateIn)
DECLARE @MonthShortName VARCHAR(3)
SET @MonthShortName = SUBSTRING(@MonthName, 1, 3)
DECLARE @YearName VARCHAR(4)
SET @YearName = DATENAME(year, @DateIn)
DECLARE @YearShortName VARCHAR(2)
SET @YearShortName = SUBSTRING(@YearName, 3, 2)
DECLARE @Hours VARCHAR(2)
SET @Hours = CASE WHEN LEN(CAST(DATEPART(hour, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(hour, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(hour, @DateIn) AS VARCHAR) END
DECLARE @Minutes VARCHAR(2)
SET @Minutes = CASE WHEN LEN(CAST(DATEPART(minute, @DateIn) AS VARCHAR)) = 2 THEN CAST(DATEPART(minute, @DateIn) AS VARCHAR) ELSE '0' + CAST(DATEPART(minute, @DateIn) AS VARCHAR) END
SET @FormattedDate = @Format
SET @FormattedDate = REPLACE(@FormattedDate, 'DD', @DateNum)
SET @FormattedDate = REPLACE(@FormattedDate, 'MM', @MonthNum)
SET @FormattedDate = REPLACE(@FormattedDate, 'MNTH', @MonthName)
SET @FormattedDate = REPLACE(@FormattedDate, 'MON', @MonthShortName)
SET @FormattedDate = REPLACE(@FormattedDate, 'YYYY', @YearName)
SET @FormattedDate = REPLACE(@FormattedDate, 'YY', @YearShortName)
SET @FormattedDate = REPLACE(@FormattedDate, 'HH', @Hours)
SET @FormattedDate = REPLACE(@FormattedDate, 'MI', @Minutes)
IF @Format = 'Standard Date & Time' AND @FormattedDate LIKE '%at 00:00' SET @FormattedDate = REPLACE(@FormattedDate, 'at 00:00', '')
RETURN @FormattedDate
END
About the poster
Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.
