Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: 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,
    Carl

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    If 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:
    Code:
    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

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by carlmal
    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
    Last edited by hmscott; 12-07-05 at 10:30.
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •