Function to format a datetime variable by a mask

  • Comments posted to this topic are about the item Function to format a datetime variable by a mask

    Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

  • Neat but I also use old style CONVERT(). seems simpler and probably more efficient. Just supply the proper [style] code.

    /*

    http://msdn.microsoft.com/en-us/library/ms187928%28v=sql.105%29.aspx

    100mon dd yyyy hh:miAM (or PM)

    101mm/dd/yyyy

    102yy.mm.dd

    103dd/mm/yyyy

    104dd.mm.yy

    105dd-mm-yy

    106dd mon yy

    107Mon dd, yy

    108hh:mi:ss

    109mon dd yyyy hh:mi:ss:mmmAM (or PM)

    110mm-dd-yy

    111yy/mm/dd

    112yymmdd

    113dd mon yyyy hh:mi:ss:mmm(24h)

    114hh:mi:ss:mmm(24h)

    120yyyy-mm-dd hh:mi:ss(24h)

    121yyyy-mm-dd hh:mi:ss.mmm(24h)

    126yyyy-mm-ddThh:mi:ss.mmm (no spaces)

    127yyyy-mm-ddThh:mi:ss.mmmZ

    130dd mon yyyy hh:mi:ss:mmmAM

    131dd/mm/yy hh:mi:ss:mmmAM

    */

    -- CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

    SELECT CONVERT(VARCHAR(24),GETDATE(),121)

  • Agreed, if you can use one of the standard styles.

    The initial triggers for me writing this were a couple of requests that didn't seem to fit in with one of those. Using a mask enables you to define an alternative format e.g. use mask 'YYYYMMDD-hhmi' to get 20131018-1645.

    Forum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g. <your code here>. You can find the IFCode tags under the INSERT options when you are writing a post.

  • Hallo,

    very good function often needed.

    German SQLservers need adaptation.

    Since Wednesday in German "Mittwoch" gets corrupted by the MI=minute mask, some workarounds are necessary. I added month and day without leading zero.

    /*

    YYYY - Year, 4 digit

    YY - Year, 2 digit

    MMMM - Monats-Name

    MMM - Monats-Name, erste 3 Buchstaben

    MM - Monat 2-stellig, führende 0

    XM - Monat ohne führende 0

    DDDD - Wochentag

    WOTAG- Wochentag

    DDD - Wochentag, erste 3 Buchstaben

    DD - Tag 2 stellig führende 0

    XD - Tag ohne führende 0

    hh - Stunde (24 Stunden, 2 stellig, führende 0

    mi - Minuten, 2 stellig, führende 0

    ss - Sekunden, 2 stellig, führende 0

    */

    alter FUNCTION [dbo].[DateFormattedByMask]

    (

    @InputDate DATETIME,

    @Mask NVARCHAR(40)

    )

    RETURNS VARCHAR(40)

    AS

    BEGIN

    SELECT @Mask = REPLACE(@Mask,'YYYY',DATENAME(YEAR,@InputDate))

    SELECT @Mask = REPLACE(@Mask,'YY',RIGHT(DATENAME(YEAR,@InputDate),2))

    SELECT @Mask = REPLACE(@Mask,'MMMM', DATENAME(MONTH,@InputDate))

    SELECT @Mask = REPLACE(@Mask,'MMM', LEFT(DATENAME(MONTH,@InputDate),3))

    SELECT @Mask = REPLACE(@Mask,'MM', RIGHT('0'+CAST(MONTH(@InputDate) AS NVARCHAR(2)),2))

    SELECT @Mask = REPLACE(@Mask,'XM', CAST(MONTH(@InputDate) AS NVARCHAR(2)))

    SELECT @Mask = REPLACE(@Mask,'DDDD', 'WOTAG')

    SELECT @Mask = REPLACE(@Mask,'DDD', LEFT(DATENAME(dw,@InputDate),3))

    SELECT @Mask = REPLACE(@Mask,'DD', RIGHT('0'+DATENAME(DAY,@InputDate),2))

    SELECT @Mask = REPLACE(@Mask,'XD', DATENAME(DAY,@InputDate))

    SELECT @Mask = REPLACE(@Mask,'hh', RIGHT('0'+DATENAME(hh,@InputDate),2))

    SELECT @Mask = REPLACE(@Mask,'mi', RIGHT('0'+DATENAME(mi,@InputDate),2))

    SELECT @Mask = REPLACE(@Mask,'ss', RIGHT('0'+DATENAME(ss,@InputDate),2))

    SELECT @Mask = REPLACE(@Mask,'WOTAG', DATENAME(dw,@InputDate))

    RETURN @Mask

    END

  • Maybe something like this could perform better, based on the following article:

    http://www.sqlservercentral.com/articles/T-SQL/91724/

    CREATE FUNCTION [dbo].[DateFormattedByMask]

    (

    @InputDate DATETIME,

    @Mask NVARCHAR(80)

    )

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT Mask = REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(

    REPLACE(@Mask,'hh', RIGHT('0'+DATENAME(hh,@InputDate),2))

    ,'mi', RIGHT('0'+DATENAME(mi,@InputDate),2))

    ,'ss', RIGHT('0'+DATENAME(ss,@InputDate),2))

    ,'YYYY',DATENAME(YEAR,@InputDate))

    ,'YY',RIGHT(DATENAME(YEAR,@InputDate),2))

    ,'MMMM', DATENAME(MONTH,@InputDate))

    ,'MMM', LEFT(DATENAME(MONTH,@InputDate),3))

    ,'MM', RIGHT('0'+CAST(MONTH(@InputDate) AS NVARCHAR(2)),2))

    ,'DDDD', DATENAME(dw,@InputDate))

    ,'DDD', LEFT(DATENAME(dw,@InputDate),3))

    ,'DD', RIGHT('0'+DATENAME(DAY,@InputDate),2))

    )

    GO

    EDIT: format and minute code issues.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thanks for the script. There have been a few of these lately and I had to go back and look yours up.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply