Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

original article:

http://sqlserver2000.databases.aspfaq.com/can-i-make-sql-server-format-dates-and-times-for-me.html

 

Many people have asked if there is a way to make SQL Server behave the way FORMAT works in VB (and FormatDateTime in VBScript). What they'd like to see is the ability to tell SQL Server to format a date with long date and time, or in MM/DD/YYYY format, instead of having to memorize existing format conversion numbers and/or manipulate the strings themselves. For example, to get today's date in YYYYMMDD format, you currently need to call the following: 
 
SELECT CONVERT(CHAR(8), GETDATE(), 112)
 
What does the 112 mean? Nothing. It's just an arbitrary number representing this specific format (Kalen Delaney's Inside SQL Server 2000 has a detailed explanation of the more commonly-used conversions). 
 
Now, wouldn't it be nice to be able to say this: 
 
SELECT CONVERT(VARCHAR, GETDATE(), 'YYYYMMDD')
 

 
Well, now you can, if you're using SQL Server 2000. I designed this scalar user-defined function for specifically this purpose. 
 
CREATE FUNCTION dbo.FormatDateTime 

    @dt DATETIME, 
    @format VARCHAR(16) 

RETURNS VARCHAR(64) 
AS 
BEGIN 
    DECLARE @dtVC VARCHAR(64) 
    SELECT @dtVC = CASE @format 
 
    WHEN 'LONGDATE' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
 
    WHEN 'LONGDATEANDTIME' THEN 
 
        DATENAME(dw, @dt) 
        + ',' + SPACE(1) + DATENAME(m, @dt) 
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2)) 
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4)) 
        + SPACE(1) + RIGHT(CONVERT(CHAR(20), 
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8), 
        @dt, 112)), 22), 11) 
 
    WHEN 'SHORTDATE' THEN 
 
        LEFT(CONVERT(CHAR(19), @dt, 0), 11) 
 
    WHEN 'SHORTDATEANDTIME' THEN 
 
        REPLACE(REPLACE(CONVERT(CHAR(19), @dt, 0), 
            'AM', ' AM'), 'PM', ' PM') 
 
    WHEN 'UNIXTIMESTAMP' THEN 
 
        CAST(DATEDIFF(SECOND, '19700101', @dt) 
        AS VARCHAR(64)) 
 
    WHEN 'YYYYMMDD' THEN 
 
        CONVERT(CHAR(8), @dt, 112) 
 
    WHEN 'YYYY-MM-DD' THEN 
 
        CONVERT(CHAR(10), @dt, 23) 
 
    WHEN 'YYMMDD' THEN 
 
        CONVERT(VARCHAR(8), @dt, 12) 
 
    WHEN 'YY-MM-DD' THEN 
 
        STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 
        5, 0, '-'), 3, 0, '-') 
 
    WHEN 'MMDDYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 10), '-', SPACE(0)) 
 
    WHEN 'MM-DD-YY' THEN 
 
        CONVERT(CHAR(8), @dt, 10) 
 
    WHEN 'MM/DD/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 1) 
 
    WHEN 'MM/DD/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 101) 
 
    WHEN 'DDMMYY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', SPACE(0)) 
 
    WHEN 'DD-MM-YY' THEN 
 
        REPLACE(CONVERT(CHAR(8), @dt, 3), '/', '-') 
 
    WHEN 'DD/MM/YY' THEN 
 
        CONVERT(CHAR(8), @dt, 3) 
 
    WHEN 'DD/MM/YYYY' THEN 
 
        CONVERT(CHAR(10), @dt, 103) 
 
    WHEN 'HH:MM:SS 24' THEN 
 
        CONVERT(CHAR(8), @dt, 8) 
 
    WHEN 'HH:MM 24' THEN 
 
        LEFT(CONVERT(VARCHAR(8), @dt, 8), 5) 
 
    WHEN 'HH:MM:SS 12' THEN 
 
        LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11)) 
 
    WHEN 'HH:MM 12' THEN 
 
        LTRIM(SUBSTRING(CONVERT( 
        VARCHAR(20), @dt, 22), 10, 5) 
        + RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3)) 
 
    ELSE 
 
        'Invalid format specified' 
 
    END 
    RETURN @dtVC 
END 
GO
 
(If you're using SQL Server 7.0, you can't create UDFs; so, I suppose you could put this logic into a stored procedure, and put the result into an output parameter.) 
 
Sample usage: 
 
DECLARE @now DATETIME 
SET @now = GETDATE() 
 
PRINT dbo.FormatDateTime(@now, 'LONGDATE') 
PRINT dbo.FormatDateTime(@now, 'LONGDATEANDTIME') 
PRINT dbo.FormatDateTime(@now, 'SHORTDATE') 
PRINT dbo.FormatDateTime(@now, 'SHORTDATEANDTIME') 
PRINT dbo.FormatDateTime(@now, 'UNIXTIMESTAMP') 
PRINT dbo.FormatDateTime(@now, 'YYYYMMDD') 
PRINT dbo.FormatDateTime(@now, 'YYYY-MM-DD') 
PRINT dbo.FormatDateTime(@now, 'YYMMDD') 
PRINT dbo.FormatDateTime(@now, 'YY-MM-DD') 
PRINT dbo.FormatDateTime(@now, 'MMDDYY') 
PRINT dbo.FormatDateTime(@now, 'MM-DD-YY') 
PRINT dbo.FormatDateTime(@now, 'MM/DD/YY') 
PRINT dbo.FormatDateTime(@now, 'MM/DD/YYYY') 
PRINT dbo.FormatDateTime(@now, 'DDMMYY') 
PRINT dbo.FormatDateTime(@now, 'DD-MM-YY') 
PRINT dbo.FormatDateTime(@now, 'DD/MM/YY') 
PRINT dbo.FormatDateTime(@now, 'DD/MM/YYYY') 
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 24') 
PRINT dbo.FormatDateTime(@now, 'HH:MM 24') 
PRINT dbo.FormatDateTime(@now, 'HH:MM:SS 12') 
PRINT dbo.FormatDateTime(@now, 'HH:MM 12') 
PRINT dbo.FormatDateTime(@now, 'goofy')
Posted on Saturday, April 26, 2008 4:59 AM SQL Server 2000 Tricks | Back to top


Comments on this post: SQL Server format dates and times

# re: SQL Server format dates and times
Requesting Gravatar...

GHD Hair Straightener. Compare GHD IV Styler Prices So You Can Buy The Cheapest GHD Straighteners Including The GHD Precious Christmas Gift Set & Pink GHDs
GHD
GHD Hair Straighteners
GHD GHD
Left by saa on Feb 23, 2010 11:49 PM

# re: SQL Server format dates and times
Requesting Gravatar...
Very helpful post, have been looking for this code.
Left by canvas prints on Dec 13, 2011 3:32 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net