Include the first zero when converting time from datime into INT

  • I have

    declare @t datetime = '2016-11-02 07:22:26.320'

    SELECT LEFT(CAST(REPLACE(CONVERT(VARCHAR, @t, 108), ':', '') AS INT), 6)

    which gives me INT value of 72226, and I want it to include the Zero, to be 072226.

    I also want to add 1 before the results, to be 1072226, even if @t datetime = '2016-11-02 14:22:26.320', the results should be 1142226

  • hoseam (11/2/2016)


    I have

    declare @t datetime = '2016-11-02 07:22:26.320'

    SELECT LEFT(CAST(REPLACE(CONVERT(VARCHAR, @t, 108), ':', '') AS INT), 6)

    which gives me INT value of 72226, and I want it to include the Zero, to be 072226.

    I also want to add 1 before the results, to be 1072226, even if @t datetime = '2016-11-02 14:22:26.320', the results should be 1142226

    declare @t datetime = '2016-11-02 14:22:26.320'

    SELECT LEFT(CAST(REPLACE(CONVERT(VARCHAR, @t, 108), ':', '') AS INT), 6) + 1000000

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • hoseam (11/2/2016)


    I have

    declare @t datetime = '2016-11-02 07:22:26.320'

    SELECT LEFT(CAST(REPLACE(CONVERT(VARCHAR, @t, 108), ':', '') AS INT), 6)

    which gives me INT value of 72226, and I want it to include the Zero, to be 072226.

    I also want to add 1 before the results, to be 1072226, even if @t datetime = '2016-11-02 14:22:26.320', the results should be 1142226

    As you know, integers won't display leading zeros so you'll need to convert to a character based representation and Phil did that for you above.

    I'll add that storing such a conversion in a table almost always leads to nothing but problems. Even MS messed up when SQL Server first came out and it continues today in things like the job history table. I'll also say doing this conversion for something like naming backup or other files is OK.

    So, my question is, WHY do you need to do this conversion?

    --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)

  • I need to return AuditDate, Date of AuditDate and Time of Audit Date. I will then be linking these to my date_time Dimension.

  • hoseam (11/2/2016)


    I need to return AuditDate, Date of AuditDate and Time of Audit Date. I will then be linking these to my date_time Dimension.

    That being the case, why wouldn't you instead use the appropriate data types (date, time)?

    A simple convert of your initial value gives you just the date, or just the time:

    select getdate() as [Getdate]

    , convert(date, getdate()) as [Date]

    , convert(time, getdate()) as [Time];

  • hoseam (11/2/2016)


    I need to return AuditDate, Date of AuditDate and Time of Audit Date. I will then be linking these to my date_time Dimension.

    As Jason suggests, I'd spend some time fixing that to use the correct datatypes for reasons of performance, memory/disk footprint size, ease of use, and validation of data. I can think of no advantages of storing TIME as you propose that aren't grossly and certainly outweighed by the disadvantages of doing so.

    --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)

  • Both Date and Time are stored as INT datatypes in my Dimensions, hence I needed to convert them to INT values.

  • hoseam (11/2/2016)


    Both Date and Time are stored as INT datatypes in my Dimensions, hence I needed to convert them to INT values.

    I understand that and THAT's what I'm saying is the fundamental problem that will cause you great pain throughout the lifetime of the database. Separating Dates and Times is also another problem that you'll live to regret.

    --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)

Viewing 8 posts - 1 through 7 (of 7 total)

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