Include the first zero when converting time from datime into INT

Question

Include the first zero when converting time from datime into INT

hoseam

SSCertifiable

Points: 5533
More actions
November 2, 2016 at 6:28 am

#400978

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

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

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)

hoseam SSCertifiable Points: 5533 More actions · Answer 3

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

jasona.work SSC Guru Points: 50118 More actions · Answer 4

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];

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

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)

hoseam SSCertifiable Points: 5533 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

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)