November 2, 2016 at 6:28 am
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
November 2, 2016 at 6:33 am
hoseam (11/2/2016)
I havedeclare @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
November 2, 2016 at 7:15 am
hoseam (11/2/2016)
I havedeclare @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
Change is inevitable... Change for the better is not.
November 2, 2016 at 7:22 am
I need to return AuditDate, Date of AuditDate and Time of Audit Date. I will then be linking these to my date_time Dimension.
November 2, 2016 at 7:31 am
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];
November 2, 2016 at 8:43 am
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
Change is inevitable... Change for the better is not.
November 2, 2016 at 11:16 pm
Both Date and Time are stored as INT datatypes in my Dimensions, hence I needed to convert them to INT values.
November 3, 2016 at 7:06 am
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply