Pages

▼

Tuesday, September 24, 2013

SQL datetime formatting function–convert to string

Illustration-Of-A-3d-Ivory-White-Man-Construction-Worker-Carrying-A-Wrench
I found this looking for something else on the Internet, but I thought it would be a great item for your toolbox. The function was originally written for 2005, but it has worked on everything I’ve tried.
Enjoy!
Many thanks to Anubhav Goyal , who provided this information to the SQL community on June 11, 2009  - You rock Anubhav! http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
/*-------------------------------------------------------------------------------------------*/
--SQL Server date formatting function --convert datetime to string
/*--------------------------------------------------------------------------------------------*/
--SQL datetime functions
--SQL Server date formats
--T-SQL convert dates
--Formatting dates sql server
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YYYY',
                         DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'YY',
                         RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Month',
                         DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
       SET @StringDate = REPLACE(@StringDate, 'MON',
                         LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'Mon',
                                     LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'MM',
                  RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'M',
                         CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'DD',
                         RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
       SET @StringDate = REPLACE(@StringDate, 'D',
                                     DATENAME(DD, @Datetime))  
RETURN @StringDate
END
GO
--Microsoft SQL Server date format function test
--MSSQL formatting dates
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')           --01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')           --03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY')            --1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY')             --1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY')               --1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY')             --01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY')         --JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY')         --Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY')       --January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')           --2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD')             --20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')           --2012-01-03
--CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD')      --12.01.03
GO
at

2 comments:

  1. AnonymousFebruary 26, 2018 at 8:31 AM

    Why the query returns to me this ?
    SELECT DISTINCT dbo.fnFormatDate (getdate()+(304), 'MON YY')
    ->27EC 18

    ReplyDelete
  2. Dynamics ConfessorMarch 14, 2018 at 5:22 PM

    Interesting,
    It doesn't work for March, May or December. Their function test results do not even work for those three months. I looked at it briefly and thought it was because we used 'MON DD' and that wasn't listed, but that isn't so. If you enter it exactly as they have it, it doesn't work for those either. I'll have to research this a bit. I know it's not as cool as their function, but the following will yield the date with the full year:

    SELECT UPPER(right(convert(VARCHAR, getdate(), 106), 8))


    --Microsoft SQL Server date format function test
    --MSSQL formatting dates
    SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY') --01/03/2012
    SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY') --03/01/2012
    SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY') --1/03/2012
    SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY') --1/3/2012
    SELECT dbo.fnFormatDate (getdate(), 'M/D/YY') --1/3/12
    SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY') --01/03/12
    SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY') --JAN 03, 2012
    SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY') --Jan 03, 2012
    SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY') --January 03, 2012
    SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD') --2012/01/03
    SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD') --20120103
    SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD') --2012-01-03
    SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY') --2012-01-03

    --CURRENT_TIMESTAMP returns current system date and time in standard internal format
    SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD') --12.01.03
    GO

    ReplyDelete