Friday, February 24, 2012

Add Date only to Time only = datetime ?

I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
CarlIf I understand you, you want use date from EVT_DT, use time from EVT_TM put them together and retrieve datetime: '2005-12-05 13:59:00'

if I'm right, transform dates to varchar, cut what u need, concatenate strings and transform it back to date time:

declare @.EVT_DT datetime
declare @.EVT_TM datetime

set @.EVT_DT = '2005-12-05 00:00:00'
set @.EVT_TM = '2005-12-06 13:59:00'

select
cast(
cast(@.EVT_DT as varchar(11)) + ' ' +
cast(datepart(hh, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(mi, @.EVT_TM) as varchar(2)) + ':' + cast(datepart(ss, @.EVT_TM) as varchar(2))
as datetime)

2005-12-05 13:59:00.000|||I have a field that contains only a date, and a field that only contains times. If I try to add the two together, I get some meaningless date like year 2111.

The raw data looks like this
EVT_DT='2005-12-05 00:00:00'
EVT_TM='2005-12-06 13:59:00' //today's date

I wrote a function that gives me the minutes past midnight for the EVT_TM
and use a dateadd(n,myMinutesFuntion(EVT_TM),EVT_DT), but it kills the performance in the nexted cursor.

Thanks,
Carl

Just so you are aware, using your examples above, SQL would "treat" EVT_DT as 38689.00000 and EVT_TM as 38690.58264. So, when you try to add them together, you're getting an unexpected value. A better practice would be to store the completed Date/Time together in a single column and then deal with the issue of selecting for a specific data by using BETWEEN.

Regards,

hmscott

No comments:

Post a Comment