October 9, 2009 at 9:49 am
I have the following data in two separate columns:
2006-08-21 00:00:00:00 and 1100
I would like it look like this:
2006-08-21 11:00:00:00
How can I do that?
October 9, 2009 at 10:06 am
Date/time calculations can be a pain, especially if the components are stored as INT's, VARCHAR, etc. The below code will take (presumably an INT representation of time), CAST it as a VARCHAR then substring out the hour and minute. The REPLICATE is in the function to make sure any integer representations like "800" are converted into "0800" first before substringing the result. Once that has been converted into a true DATETIME, the time is added to the original date.
Yes, there are probably much better ways to do this :w00t:
CREATE TABLE dbo.DateCombine(
DatePortion DATETIME NOT NULL,
TimePortion INT NOT NULL)
INSERT INTO dbo.DateCombine(DatePortion, TimePortion) VALUES('2006-08-21', '1100')
SELECT
--Truncated date
d.DatePortion +
--Parse time from integer value and add time portion to date portion
CAST(SUBSTRING(REPLICATE('0', 4 - LEN(CAST(d.TimePortion AS VARCHAR(4)))) + CAST(d.TimePortion AS VARCHAR(4)), 1, 2)
+ ':' +
SUBSTRING(REPLICATE('0', 4 - LEN(CAST(d.TimePortion AS VARCHAR(4)))) + CAST(d.TimePortion AS VARCHAR(4)), 3, 2) AS DATETIME)
FROM dbo.DateCombine d
MJM
October 9, 2009 at 11:47 am
Thanks!!!! That works prefect!!
Chad
October 9, 2009 at 12:19 pm
select
NewDate = dateadd(hour,a.TM/100,dateadd(minute,a.TM%100,a.DT))
from
( -- Test Data
select DT = convert(datetime,'2006-08-21'), TM = 1123
) a
Results:
NewDate
-------------------------
2006-08-21 11:23:00.000
(1 row(s) affected)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply