How to get YYYYMMDDHHMMSS

  • This is getting the data I need, but not the format.

    select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

    10212021102437

    I want YYYYMMDD before the time portion.

    Thanks.

  • Thanks!!

  • You can cut out one of the REPLACEs.  Look at format # 112.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • p.s.  Using VARCHAR with no length is one of those code smells that you might want to avoid.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Replace all that with

    SELECT FORMAT(GETDATE(),'yyyyMMddhhmmss')

  • You could use 120 and then replace the dashes, colons and space with ''.  Is 3 REPLACE's worse than calling GETDATE twice?

     

     

  • planetmatt wrote:

    Replace all that with

    SELECT FORMAT(GETDATE(),'yyyyMMddhhmmss')

    You need to be really careful there.  FORMAT is about 43 times slower than just about anything you might want to do with CONVERT.  It's been proven many times.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden wrote:

    p.s.  Using VARCHAR with no length is one of those code smells that you might want to avoid.

    It would default to varchar(30) , but still to wide for a normal iso datetime.

    ----------------------------------------------------

  • This takes 3 replaces - but gets it done without concatenation:

    Select convertedtime = replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', '')

    The problem with all of these is the return value from replace is varchar(8000).  If you need a specific data type then you need to add an additional cast/convert around the result.

    I also don't like using varchar when we know the length of the input won't change.  For all dates/times - getdate() with a format of 120 will always return 19 characters, and that tells us that the final data type should be char(19) also:

    Select convertedtime = cast(replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', '') as char(19))
    Select convertedtime = convert(char(19), replace(replace(replace(convert(char(19), getdate(), 120), ':', ''), ' ', ''), '-', ''))

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • MMartin1 wrote:

    Jeff Moden wrote:

    p.s.  Using VARCHAR with no length is one of those code smells that you might want to avoid.

    It would default to varchar(30) , but still to wide for a normal iso datetime.

    Yep.  I know about the default.  In other cases (and I forget what they are but don't worry about them because I NEVER rely on the defaults), they default to 1.

    You do hint at the reason in your note... data type disparity, which also leads to things like very expensive datatype mismatches in JOIN and WHERE criteria.  It frequently also means that someone was too lazy to do their homework to "right-size" their datatypes.

    I do 100% reviews on code in our shop and using any variable-width datatype with no indication of size will always be sent back to the developer for proper sizing.  As you say, a "30" would not be correct here and would also fail review.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • A lot of the methods posed so far do the job but are actually relatively slow because of all the string manipulation using both concatenation and REPLACE.  Using a nice general binary collation cuts about 30% of the time out but that's still relatively slow.

    A pure math and a hybrid CONVERT with a couple of STUFFs results in a 50-55% improvement (twice as fast).

    CONVERT, by itself, is nasty fast.  As a bit of a sidebar, anything and everything is at least 7 times faster than (ugh!) FORMAT (FORMAT took 73 seconds on a 10 million row table).  Don't use FORMAT for anything in SQL Server even for small stuff.  It will become a part of the "Death by a Thousand Cuts" performance issues that most servers suffer.

    If you really want performance to go along with accuracy and still be relatively simple, try the following.  The added benefit is that the result can be stored in an 8 bit BIGINT instead of a 14 byte CHAR(14).  For a 10 million row table, the other code posted took 10.4 seconds to run.  The code below only takes about 3.6 seconds or about 3 times faster.

     SELECT YYYYMMDDHHMISS = CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000
    + DATEPART(hh,GETDATE())*10000
    + DATEPART(mi,GETDATE())*100
    + DATEPART(ss,GETDATE())
    ;

    If you really need the output to be character based and you're dumping it to a table,  the the implicit conversion to a CHAR(14) column will only slow the code above down to about 3.9 seconds.  If you want the nice warm fuzzies that avoiding implicit conversions provides, the following will do the trick with no addition time added.

     SELECT YYYYMMDDHHMISS = CONVERT(CHAR(14),
    CONVERT(BIGINT,CONVERT(CHAR(8),GETDATE(),112))*1000000
    +DATEPART(hh,GETDATE())*10000
    +DATEPART(mi,GETDATE())*100
    +DATEPART(ss,GETDATE())
    )
    ;

    Yeah... I know what a lot of folks are thinking.  "Big deal!  3.6 seconds v.s. 10.4 on 10 MILLION rows!  WHO CARES!!!????).

    The answer is, everyone should... all the time.  Just imagine if ALL your code ran almost 3 times faster  with almost 3 times less CPU than the stuff you have running now.   Heh... and if your stuff is in the cloud, thing of the money you might save not only on processors but on SQL Server licenses, as well.

    "Make it work, make it fast, make it pretty... and it ain't done 'til it's pretty!"

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Bruin wrote:

    This is getting the data I need, but not the format.

    select replace(convert(varchar, getdate(),101),'/','') + replace(convert(varchar, getdate(),108),':','')

    10212021102437

    I want YYYYMMDD before the time portion.

    Thanks.

    Just out of sheer curiosity, why do you need to do this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Data type disparity on join conditions etc is not really an after thought I've seen over time, its more like a 'no thought.' You bring up an excellent point that a simple adjustment here should be done to pass code review.

    And just a fun fact regarding char/varchar, from Microsoft docs, > "When n isn't specified in a data definition or variable declaration statement, the default length is 1. If n isn't specified when using the CAST and CONVERT functions, the default length is 30."

    ----------------------------------------------------

  • Thank you for the great feedback and the quote from BOL.

    Now if we could get the OP to cough up the reason for wanting to do this task. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 15 posts - 1 through 15 (of 16 total)

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