Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    11

    Unanswered: If the time is Thus, than make the timedate Thus

    Here is an odd problem (to me).

    I need to measure the time between timefield_a and timefield_b. So, I used
    DATEDIFF (hh, timefield_a, timefield_b)

    Now, they said "Our clock starts at 7am, so even if someone enters timefield_a at 5am or 630am, I want that rounded up to 7am, so my clock doesn't start till then".

    So, I've been trying to create a case statement that rounds the time up to 7am but still keeps the date in tact. No luck.

    Any thoughts?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    This seems to work:

    Code:
    declare @date1 DATETIME
    DECLARE @DATE2 DATETIME
    set @date1 ='1/1/2006 6:37 am'
    SET @DATE2= DATEADD(hh,7,CAST(MONTH(@Date1) AS CHAR(2)) + '/' + CAST(DAY(@Date1) AS CHAR(2))+ '/' + CAST(YEAR(@Date1) AS CHAR(4)))
    SELECT 'dif' = case when @date1<@date2 then @date2 else @date1 end
    Inspiration Through Fermentation

Posting Permissions

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