Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 how to combine and convert two integer columns to datetime Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, March 15, 2013 2:13 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 27, 2013 2:26 AM Points: 167, Visits: 197
 I have date stored in a column as integer type and time stored in a column as integer type. How do I combine and convert both columns into datetime type?For Example: Col A has 20130314 (yyyymmdd) and Col B has 123000 (hhmmss). My Output should be 2013-03-14 12:30:00
Post #1431741
 Posted Friday, March 15, 2013 2:20 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 7:48 PM Points: 22,095, Visits: 29,028
 naveen.pasupuleti (3/15/2013)I have date stored in a column as integer type and time stored in a column as integer type. How do I combine and convert both columns into datetime type?For Example: Col A has 20130314 (yyyymmdd) and Col B has 123000 (hhmmss). My Output should be 2013-03-14 12:30:00Something like this:`declare @TestDate int = 20130314, @TestTIme int = 123000;select cast(cast(@TestDate as varchar(8)) as datetime), cast(stuff(stuff(cast(@TestTime as varchar(6)),5,0,':'),3,0,':') as time(0)), cast(cast(@TestDate as varchar(8)) as datetime) + cast(stuff(stuff(cast(@TestTime as varchar(6)),5,0,':'),3,0,':') as time(0));`
Post #1431746
 Posted Friday, March 15, 2013 3:00 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 27, 2013 2:26 AM Points: 167, Visits: 197
 Thanks Lynn for your response. The code you shared works for this example but sometimes integer time column or col B can have values like 93000 (9.30am) or 0 (12 am). The number of digits vary in the column. SELECT last_run_date,last_run_time from msdb.dbo.sysjobserversOne closest example I can think of is from msdb database. Sysjobservers table has two columns that match exactly my requirement. Last_run_date column is a integer type having date number and Last_run_time is a integer type having time number. How to get datetime out of these two columns.
Post #1431771
 Posted Friday, March 15, 2013 3:06 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 7:48 PM Points: 22,095, Visits: 29,028
 naveen.pasupuleti (3/15/2013)Thanks Lynn for your response. The code you shared works for this example but sometimes integer time column or col B can have values like 93000 (9.30am) or 0 (12 am). The number of digits vary in the column. SELECT last_run_date,last_run_time from msdb.dbo.sysjobserversOne closest example I can think of is from msdb database. Sysjobservers table has two columns that match exactly my requirement. Last_run_date column is a integer type having date number and Last_run_time is a integer type having time number. How to get datetime out of these two columns. Try this:`declare @TestDate int = 20130314, @TestTIme int = 123000;select cast(cast(@TestDate as varchar(8)) as datetime), cast(stuff(stuff(cast(@TestTime as varchar(6)),5,0,':'),3,0,':') as time(0)), cast(cast(@TestDate as varchar(8)) as datetime) + cast(stuff(stuff(right('000000' + cast(@TestTime as varchar(6)),6),5,0,':'),3,0,':') as time(0));`
Post #1431778
 Posted Friday, March 15, 2013 3:22 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 2:45 PM Points: 1,574, Visits: 2,217
 Or, another possibility (with no character conversions/manipulation):`SELECT ColA, ColB, DATEADD(SECOND, ColB % 100, DATEADD(MINUTE, ColB % 10000 / 100, DATEADD(HOUR, ColB / 10000, DATEADD(DAY, ColA % 100 - 1, DATEADD(MONTH, ColA % 10000 / 100 - 1, DATEADD(YEAR, ColA / 10000 - 1900, 0)))))) AS Col_NewFROM ( SELECT 20130314 AS ColA, 123011 AS ColB UNION ALL SELECT 20130314, 093015) AS test_data` SQL DBA,SQL Server MVP('07, '08, '09)One man with courage makes a majority. Andrew Jackson
Post #1431794
 Posted Friday, March 15, 2013 3:32 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:30 PM Points: 7,062, Visits: 12,465
 Are these SQL Agent date and time values? I used to have my own homegrown functions but I just picked up knowledge of this lttle gem on these forums the other day:`SELECT msdb.dbo.agent_datetime(20130314, 123000);` __________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --PlatoBelieve you can and you're halfway there. --Theodore RooseveltEverything Should Be Made as Simple as Possible, But Not Simpler --Albert EinsteinThe significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1431802
 Posted Friday, March 15, 2013 3:48 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:30 PM Points: 7,062, Visits: 12,465
 PS Forgot to mention the function is undocumented so take that into consideration. __________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --PlatoBelieve you can and you're halfway there. --Theodore RooseveltEverything Should Be Made as Simple as Possible, But Not Simpler --Albert EinsteinThe significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1431814
 Posted Monday, March 18, 2013 9:29 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 27, 2013 2:26 AM Points: 167, Visits: 197
 Thank you very much OPC.Three. This builtin function msdb.dbo.agent_datetime did the job for me.
Post #1432231
 Posted Monday, March 18, 2013 9:37 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 10:30 PM Points: 7,062, Visits: 12,465
 You're welcome. Thanks for the feedback. __________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --PlatoBelieve you can and you're halfway there. --Theodore RooseveltEverything Should Be Made as Simple as Possible, But Not Simpler --Albert EinsteinThe significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1432234
 Posted Monday, March 18, 2013 10:05 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:41 AM Points: 6,280, Visits: 12,097
 opc.three (3/15/2013)PS Forgot to mention the function is undocumented so take that into consideration.It's a cough scalar function too. Combine the best bits of Lynn's and Scott's solutions and roll them into an inline TVF:`CREATE FUNCTION [dbo].[IF_Agent_Datetime] ( @Date INT, @Time INT)RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT DatetimeValue = DATEADD(SECOND,@Time % 100, DATEADD(MINUTE,@Time % 10000 / 100, DATEADD(HOUR,@Time/10000, CAST(CAST(@Date AS CHAR(8)) AS DATETIME)))) )GOSELECT * FROM dbo.IF_Agent_Datetime (20130314, 123011)2013-03-14 12:30:11.000` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps
Post #1432246

 Permissions