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
 Old Hand Group: General Forum Members Last Login: Thursday, December 1, 2016 6:12 PM Points: 358, Visits: 403
 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 Thanks,Naveen.Every thought is a cause and every condition an effect
Post #1431741
 Posted Friday, March 15, 2013 2:20 PM
 SSC-Insane Group: General Forum Members Last Login: 2 days ago @ 4:50 PM Points: 23,515, Visits: 37,731
 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
 Old Hand Group: General Forum Members Last Login: Thursday, December 1, 2016 6:12 PM Points: 358, Visits: 403
 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. Thanks,Naveen.Every thought is a cause and every condition an effect
Post #1431771
 Posted Friday, March 15, 2013 3:06 PM
 SSC-Insane Group: General Forum Members Last Login: 2 days ago @ 4:50 PM Points: 23,515, Visits: 37,731
 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
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 3:56 PM Points: 3,852, Visits: 6,566
 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)Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1431794
 Posted Friday, March 15, 2013 3:32 PM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 3:25 AM Points: 7,933, Visits: 14,355
 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. --Plato
Post #1431802
 Posted Friday, March 15, 2013 3:48 PM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 3:25 AM Points: 7,933, Visits: 14,355
 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. --Plato
Post #1431814
 Posted Monday, March 18, 2013 9:29 AM
 Old Hand Group: General Forum Members Last Login: Thursday, December 1, 2016 6:12 PM Points: 358, Visits: 403
 Thank you very much OPC.Three. This builtin function msdb.dbo.agent_datetime did the job for me. Thanks,Naveen.Every thought is a cause and every condition an effect
Post #1432231
 Posted Monday, March 18, 2013 9:37 AM
 SSCertifiable Group: General Forum Members Last Login: 2 days ago @ 3:25 AM Points: 7,933, Visits: 14,355
 You're welcome. Thanks for the feedback. __________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1432234
 Posted Monday, March 18, 2013 10:05 AM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, December 1, 2016 10:11 AM Points: 8,587, Visits: 18,753
 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