Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Advice on how to combine date and time into one field

    I'm looking for help to combine a date and time field to make one field.

    We have a piece of software and database for student registers.

    One of the biggest problems is the database has been difficult to work with, as I have had to work out the date using dateadd functions and combine fields from different tables.
    I've now got the date correctly, but I want to add the time to the time part of a datetime field.

    The time field is already stored in a datetime field, but the date in this field is always 1899-12-30.

    This is the function I am using to get the date:
    Code:
    DATEADD(dd, e.day_of_week, DATEADD(ww, sm.week, '01/08/2011'))
    And this is the function I use to get the time (from a datetime field):
    Code:
    SUBSTRING(CAST(e.start_time AS varchar(20)), 13, 5)
    With the DATEADD function it appears you can only add individual elements each time hh, mi, ss.

    Would I have to add a DATEADD function for each time element in order to add the time to the date?
    <- Hides behind a rock.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There are a couple ways to do this, depending on the precision you need, and the version you have. If you have SQL 2008 or better, you can just use the TIME datatype to take the time out. Then you can add that to to the datetime representation of the date.
    Code:
    declare @timeportion datetime
    
    set @timeportion = '1899/12/31 12:38'
    
    select convert(time, @timeportion), convert(datetime, '1/1/2012') + convert(time, @timeportion)
    If you are on SQL 2005 or lower, then you will need to derive how many seconds past midnight the time is, and add that to your date.
    Code:
    declare @timeportion datetime
    
    set @timeportion = '1899/12/31 12:38'
    
    select datediff (ss, '1899/12/31', @timeportion), dateadd (ss, datediff (ss, '1899/12/31', @timeportion), '1/1/2012')
    Naturally, you can use milliseconds, if you need that level of precision. Most reports don't need that level of precision, though. Most get away with minutes.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When dealing with hinky data, I always take the ultra-conservative approach to the fix. I would use:
    Code:
    DateAdd(day, DateDiff(day, e.start_time, '2011-01-08t00:00') 
    +  7 * sm.[week] + e.day_of_week, e.start_time)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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