February 13, 2014 at 3:54 pm
Hello Everyone
I hope that your day is going a bit better than mine
I am working on some really old and funky data. I have a valid date in a column, but the time is stored in the next column, and is stored as an integer. The integer represents the number of seconds past midnight from the date in the datetime column.
I need some assistance with two things actually. How do I convert the integer into a valid time of day? And How can I append that time to the date in the ApptDate column?
DECLARE @DateAndTime TABLE
(
ApptDate datetime
, ApptTime int
)
INSERT INTO @DateAndTime
SELECT '2014-01-10 00:00:00.000','68700' UNION ALL
SELECT '2014-04-04 00:00:00.000','34200' UNION ALL
SELECT '2014-01-15 00:00:00.000','37800' UNION ALL
SELECT '2014-01-10 00:00:00.000','46800' UNION ALL
SELECT '2014-02-03 00:00:00.000','29700' UNION ALL
SELECT '2014-01-17 00:00:00.000','33300' UNION ALL
SELECT '2014-01-08 00:00:00.000','40500' UNION ALL
SELECT '2014-01-08 00:00:00.000','31500' UNION ALL
SELECT '2014-01-16 00:00:00.000','53100' UNION ALL
SELECT '2014-01-08 00:00:00.000','59400'
SELECT * FROM @DateAndTime
I have never tried either, but everything that I can come up with is not correct.
Thank you in advance, and I greatly appreciate your time, assistance, comments and code suggestions.
Andrew SQLDBA
February 13, 2014 at 3:59 pm
Really simple actually:
DECLARE @DateAndTime TABLE
(
ApptDate datetime
, ApptTime int
)
INSERT INTO @DateAndTime
SELECT '2014-01-10 00:00:00.000','68700' UNION ALL
SELECT '2014-04-04 00:00:00.000','34200' UNION ALL
SELECT '2014-01-15 00:00:00.000','37800' UNION ALL
SELECT '2014-01-10 00:00:00.000','46800' UNION ALL
SELECT '2014-02-03 00:00:00.000','29700' UNION ALL
SELECT '2014-01-17 00:00:00.000','33300' UNION ALL
SELECT '2014-01-08 00:00:00.000','40500' UNION ALL
SELECT '2014-01-08 00:00:00.000','31500' UNION ALL
SELECT '2014-01-16 00:00:00.000','53100' UNION ALL
SELECT '2014-01-08 00:00:00.000','59400'
SELECT * FROM @DateAndTime
update @DateAndTime set
ApptDate = dateadd(ss, ApptTime, ApptDate);
select * from @DateAndTime;
February 13, 2014 at 4:04 pm
Lynn
Thank You so Very much. I was trying to make this so much more difficult.
I am so embarrassed, I am not going to show or tell what path I was going down.
Thanks again
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy