November 20, 2012 at 9:34 am
Patick,
One small doubt.I have to concatenate one more time_column to this date_column.
This time column is also defined as a decimal ,I'm sure even its code is gonna be similar to the one below
The original format is HHMMSS and I want it in HH:MM:SS.I have displayed the output below
sELECT
CASE WHEN MY_DATE_COLUMN> 19000000 AND MY_DATE_COLUMN < 21000000 THEN
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,MY_DATE_COLUMN)))
ELSE CONVERT(DATETIME,'01/01/1900')
END NEW_DATE_COLUMN
FROM MY_TABLE
MY_time_column
123658
171420
102137
102704
111639
162821
105001
84814
120505
122223
170520
92403
93551
95229
121919
123625
102755
94051
95618
101142
141911
144111
151129
151832
114125
122232
133559
134540
143021
144003
144034
182804
130436
151645
143958
144222
143702
150247
151112
134007
0
Thx
SM
November 20, 2012 at 10:05 am
Both this post and the previous post obviously don't validate that the results are legitimate times and dates. That is probably a nice discussion to have, hope you are watching out for that !!!!
If I were converting the time column from integer, I'd do something like this, obviously the zeros will convert to '00:00:00' so thats something you could maybe check for with another case statement?
SELECT SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),2,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),4,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),6,2)
FROM ORIGINAL_TABLE
November 20, 2012 at 10:10 am
An example just to underscore the validation point, I could put 139001 into the time column and while its a perfectly legitimate integer (or decimal (10,0)), if you applied these conversions we're discussing here, simplistic routines like these will happily produce these values and they'll be INVALID DATES AND TIMES THAT COULD CAUSE PROBLEMS FURTHER DOWN THE LINE!!!!
Just a heads up to keep an eye out for!!!!
November 20, 2012 at 10:12 am
Patrick,
Thank you one more time for bailing me out.Even this works perfectly like the one you gave before.
Thnx
Swarup
November 20, 2012 at 10:16 am
SQL server rokee (11/20/2012)
Patrick,Thank you one more time for bailing me out.Even this works perfectly like the one you gave before.
Thnx
Swarup
You're welcome! Make sure you keep in mind the validation issues as decimal columns don't care if they contain valid convertable dates and times !!!
November 20, 2012 at 10:18 am
Thank u for the tip 🙂
November 20, 2012 at 11:06 am
I tried concatenting the derived columns using +' '+, unfortunately it gives error
SELECT
CASE WHEN I353_DATE_TRANSACTION > 19000000 AND I353_DATE_TRANSACTION < 21000000 THEN
CONVERT(VARCHAR(12),
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,I353_DATE_TRANSACTION)))
,101)
ELSE '01/01/1900'
END DATE +' '+SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),2,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),4,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + [I353_TIME_TRANSACTION]),6,2)as Time
FROM My_table
November 20, 2012 at 12:09 pm
Here's a solution that will do all the SQL work in one go:
IF OBJECT_ID(N'tempdb..#T') IS NOT NULL
DROP TABLE #T;
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
Col1 DECIMAL(8, 0),
Col2 DECIMAL(8, 0));
INSERT INTO #T
(Col1, Col2)
VALUES (20121120, 134010),
(20121119, 93000),
(0,12),
(19870606, 72);
-- Don't use this, it's just to show the component parts
SELECT Col1,
Col2,
CAST(CAST(NULLIF(Col1, 0) AS CHAR(8)) AS DATETIME),
CAST(Col2 AS INT) / 10000, -- Hours
(CAST(Col2 AS INT) - CAST(Col2 AS INT) / 10000 * 10000) / 100, -- Minutes
CAST(Col2 AS INT) - CAST(Col2 AS INT) / 100 * 100 -- Seconds
FROM #T;
-- Use your columns and table instead of #T, Col1, Col2
SELECT DATEADD(SECOND, CAST(Col2 AS INT) - CAST(Col2 AS INT) / 100 * 100,
DATEADD(MINUTE, (CAST(Col2 AS INT) - CAST(Col2 AS INT) / 10000 * 10000) / 100,
DATEADD(HOUR, CAST(Col2 AS INT) / 10000, CAST(CAST(NULLIF(Col1, 0) AS CHAR(8)) AS DATETIME))))
FROM #T;
The first Select is just to demonstrate how each part of the date and time is parsed out of the numbers. Then it uses DateAdd to put them all together. The final Select is moderately complex, because of all the nesting, so I put in the first part to show how each works.
If you want the date formatted, I recommend doing that above the database layer. That's what the presentation layer is for, and that should be part of the application. Formatting dates and times shouldn't be done in the database or the query. Doing it above that layer allows for localization, internationalization, and personal preferences.
For example, I prefer DD-MM-YYYY or YYYY-MM-DD over MM-DD-YYYY, even though I live in the US. So I can set Windows to present dates that way I want them, and most applications will do that for me. The people at desks near mine prefer MM-DD-YYYY, or MM-DD-YY, and they can choose that, and the same applications will give them what they want. Put the formatting in the database or in the data access layer (a query), and that option goes away and either I'm forced to guess what date "05-12-2012" is (is that 12 May or 5 Dec?), or other people are forced to guess about that. Do it the right way, and I know exactly what date it is (5 Dec), and others will see it as "12-05-2012" and know what date it is (5 Dec).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 20, 2012 at 12:53 pm
I forgot to meantion that datatype of MY_DATE_COLUMN should have been datetime ,for the code below I'm getting the result in the right format..ie but the column MY_DATE_COLUMN has become string type..how can i convert it to datetime without changing the format of the output.
I did try using Convert(datetime,followed by the below code)..but it changes the format of display..any suggestions guys
CASE WHEN MY_DATE_COLUMN > 19000000 AND MY_DATE_COLUMN < 21000000 THEN
CONVERT(VARCHAR(12),
CONVERT(DATETIME,CONVERT(VARCHAR,CONVERT(INT,MY_DATE_COLUMN)))
,101)
ELSE '01/01/1900'
END + ' ' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),2,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),4,2) + ':' +
SUBSTRING(CONVERT(VARCHAR(12),1000000 + MY_TIME_COLUMN),6,2)as MY_DATE_COLUMN
FROM MY_TABLE
November 20, 2012 at 1:03 pm
GSquared actually already gave the bottom line here:
"If you want the date formatted, I recommend doing that above the database layer."
Formatting it with the convert functions we use required that we have VARCHAR as the resulting datatype, otherwise the datetime will get formatted exactly as the program doing the displaying wants to, in this case I assume you're using SQL Studio. The reason VARCHAR lets you format the datetime the way you want, is that the resulting displaying program then will interpret the value literally. It doesn't try to rearrange VARCHAR output (well at least to the extent of trying to interpret what the characters mean).
November 20, 2012 at 1:08 pm
Well, I'm not authorized to make changes in the original table...so this is the only option I'm left with ;(
November 20, 2012 at 1:20 pm
I know what you mean about being stuck with the table and column formats. I'm in the same boat. When I bring these nondate data into other applications, I use the convert functions to convert them to date types. Since SSRS is often my displaying program, I leave them as (or convert them to) datetime and let the reporting software do the formatting. I certainly do use the convert tricks however as I have the same oddball misused column types (date in integers, etc) but I deal with it (as you are also).
You might share what your end goal is and folks might offer you some advice on which way to go. While I and others had some hints on converting, what we offered might not even be suitable to what you need to do.
November 20, 2012 at 1:30 pm
Thank you for the promt response Patrick,
Currently I'm creating views which would be mapped to tables in prod server using a ETL tool. Though this particular ETL is capable of making transformations ,unfortunately plans are to replace this ETL tool with a different ETL tool
So I have make all the transformations using T-sql.
In future, plan is to use a different ETL tool so they want the datatypes in the views to be same as that of the destination.
'
This is a real pain in the neck, But I have to deal with it
Thx
SM
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply