INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

User-Defined Functions

fn_formatdatetime (Format a datetime variable with a user-defined string) by SlashZero
Posted: 18 Sep 03 (Edited 11 Apr 04)

This UDF will take a datetime variable and a varchar format string variable.  It will then pass back the date/time formated according to the format string.  This is similar to the CONVERT function, but you are not limited to the predefined types of it.  It works more like the Format function of VB/VBA.  Read the comment section of the function for details on forming the format string.  I hope this helps!  (I'm still new to writing Transact-SQL stuff so this may not be structred the best way possible, but it still works good.)

CODE

CREATE FUNCTION fn_formatdatetime (@datetime datetime, @formatstr varchar(255))  
RETURNS varchar(255)
AS
BEGIN
    -- Author: Nickolas L. Hook
    -- This Function works similar to the Format function in Visual Basic for creating Custom Formating Strings for Date/Time Variables
    -- Valid characters for the @formatstr Are...
    -- d        Displays the day as a number without a leading zero (1 - 31)
    -- dd        Displays the day as a number with a leading zero (01 - 31)
    -- ddd        Displays the day as an abbreviation (Sun - Sat)
    -- dddd        Displays the day as a full name (Sunday - Saturday)
    -- w        Displays the day of the week as a number (1 for Sunday through 7 for Saturday)
    -- m        Displays the month as a number without a leading zero (1 - 12)
    -- mm        Displays the month as a number with a leading zero (01 - 12)
    -- mmm        Displays the month as an abbreviation (Jan - Dec)
    -- mmmm    Displays the month as a full month name (January - December)
    -- yy        Displays the year as a 2-digit number (00-99)
    -- yyyy        Displays the year as a 4-digit number (1000 - 9999)
    -- q        Displays the quarter of the year (1 - 4)
    -- h        Displays the hour as a number without leading zeros (0 - 23)
    -- hh        Displays the hour as a number with leading zeros (00 - 23)
    -- th        Displays the hour as a number without leading zeros (1 - 12)
    -- n        Displays the minute as a number without leading zeros (0 - 59)
    -- nn        Displays the minute as a number with leading zeros (00-59)
    -- s        Displays the second as a number without leading zeros (0 - 60)
    -- ss        Displays the second as a number with leading zeros (00 - 60)
    -- am/pm    Displays am before noon; Displays pm after noon through 11:59 P.M.
    -- a/p        Displays a before noon; Displays p after noon through 11:59 P.M.
    --    Examples (assuming a date of March 7th, 2003 at 8:07:05 A.M.)
    --    @formatstr        Returns
    --    m/d/yy            3/7/03
    --    mmmm d, yyyy        March 7, 2003
    --    mm-dd-yyyy h:nnam/pm    03-07-2003 8:07am
    
    DECLARE @outStr    varchar(255)
    DECLARE @datestr    varchar(24)
    DECLARE @meridian    varchar(1)
    DECLARE @temp    varchar(2)
    
    SET @outStr = @formatstr
    SET @datestr = CONVERT(varchar(24), @datetime, 113)

    -- dddd --
    SET @outStr = REPLACE(@outStr, 'dddd',
        CASE DATEPART(dw, @datetime)
            WHEN 1 THEN 'Sunday'
            WHEN 2 THEN 'Monday'
            WHEN 3 THEN 'Tuesday'
            WHEN 4 THEN 'Wednesday'
            WHEN 5 THEN 'Thursday'
            WHEN 6 THEN 'Friday'
            WHEN 7 THEN 'Saturday'
        END)

    -- ddd --
    SET @outStr = REPLACE(@outStr, 'ddd',
        CASE DATEPART(dw, @datetime)
            WHEN 1 THEN 'Sun'
            WHEN 2 THEN 'Mon'
            WHEN 3 THEN 'Tue'
            WHEN 4 THEN 'Wed'
            WHEN 5 THEN 'Thu'
            WHEN 6 THEN 'Fri'
            WHEN 7 THEN 'Sat'
        END)
    -- dd --
    SET @outStr = REPLACE(@outStr, 'dd', SUBSTRING(@datestr,1,2))
    -- d --
    SET @outStr = REPLACE(@outStr, 'd', CONVERT(int,SUBSTRING(@datestr,1,2)))
    -- w --
    SET @outStr = REPLACE(@outStr, 'w', DATEPART(dw,@datetime))
    -- yyyy --
    SET @outStr = REPLACE(@outStr, 'yyyy', SUBSTRING(@datestr,8,4))
    -- yy --
    SET @outStr = REPLACE(@outStr, 'yy', SUBSTRING(@datestr,10,2))
    -- q --
    SET @outStr = REPLACE(@outStr, 'q', DATEPART(q,@datestr))
    -- hh --
    SET @outStr = REPLACE(@outStr, 'hh', SUBSTRING(@datestr,13,2))
    -- th --
    IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
    SET @outStr = REPLACE(@outStr, 'th', CONVERT(int,SUBSTRING(@datestr,13,2)) - 12)
    ELSE SET @outStr = REPLACE(@outStr, 'th', SUBSTRING(@datestr,13,2))
    -- h --
    SET @outStr = REPLACE(@outStr, 'h', CONVERT(int,SUBSTRING(@datestr,13,2)))
    -- nn --
    SET @outStr = REPLACE(@outStr, 'nn', SUBSTRING(@datestr,16,2))
    -- n --
    SET @outStr = REPLACE(@outStr, 'n', CONVERT(int,SUBSTRING(@datestr,16,2)))
    -- ss --
    SET @outStr = REPLACE(@outStr, 'ss', SUBSTRING(@datestr,19,2))
    -- s --
    SET @outStr = REPLACE(@outStr, 's', CONVERT(int,SUBSTRING(@datestr,19,2)))

    -- m, mm, mmm, mmmm (This is last because it put letters back into the @outStr and if done previously, things like the 'h' in 'March' become an hour --
    IF CHARINDEX('m',@outStr,0) > 0 BEGIN
        IF CHARINDEX('mm',@outStr,0) > 0 BEGIN
            IF CHARINDEX('mmm',@outStr,0) > 0 BEGIN
                IF CHARINDEX('mmmm',@outStr,0) > 0 BEGIN
                    SET @outStr = REPLACE(@outStr, 'mmmm',
                        CASE DATEPART(mm, @datetime)
                            WHEN 1 THEN 'January'
                            WHEN 2 THEN 'February'
                            WHEN 3 THEN 'March'
                            WHEN 4 THEN 'April'
                            WHEN 5 THEN 'May'
                            WHEN 6 THEN 'June'
                            WHEN 7 THEN 'July'
                            WHEN 8 THEN 'August'
                            WHEN 9 THEN 'September'
                            WHEN 10 THEN 'October'
                            WHEN 11 THEN 'November'
                            WHEN 12 THEN 'December'
                        END)
                END
                ELSE SET @outStr = REPLACE(@outStr, 'mmm', SUBSTRING(@datestr,4,3))
            END
            ELSE BEGIN
                SET @temp = DATEPART(mm,@datetime)
                IF (DATEPART(mm,@datetime)<10) SET @temp = '0' + @temp
                SET @outStr = REPLACE(@outStr, 'mm', @temp)
            END

        END
        ELSE BEGIN
            SET @outStr = REPLACE(@outStr, 'm', DATEPART(mm,@datetime))
            SET @outStr = REPLACE(@outStr, 'a'+CAST(DATEPART(mm,@datetime) AS varchar(1))+'/p'+CAST(DATEPART(mm,@datetime) AS varchar(1)),'am/pm')
        END
    END

    -- Used by am/pm and a/p --
    IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
    SET @meridian = 'p'
    ELSE SET @meridian = 'a'
    -- am/pm --
    SET @outStr = REPLACE(@outStr, 'am/pm', @meridian+'m')
    -- a/p --
    SET @outStr = REPLACE(@outStr, 'a/p', @meridian)
    
    RETURN @outStr
END

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close