Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123

    Unanswered: Updating Time of smalldatetime field

    How does one update the time part of a smalldatetime field...?
    2006-11-16 20:12:00 ---> 2006-11-16 16:30:00
    2005-06-01 18:19:00 ---> 2005-06-01 16:30:00

    I have tried using the datepart but I'm doing something incorrectly.

    thanks,

    Jonathan

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by jdmarsh2g
    How does one update the time part of a smalldatetime field...?
    2006-11-16 20:12:00 ---> 2006-11-16 16:30:00
    2005-06-01 18:19:00 ---> 2005-06-01 16:30:00

    I have tried using the datepart but I'm doing something incorrectly.

    thanks,

    Jonathan

    Don't you want to be doing something with DateAdd?

    Like
    Code:
    -- Add five hours
    UPDATE MyTable SET MyTime = DateAdd(hh, 5, MyTime)
    GO
    
    -- Add five minutes
    UPDATE MyTable SET MyTime = DateAdd(mi, 5, MyTime)
    GO
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You could either use DATEADD or stuff the exact time portion you want into it. Are they all going to be 16:30?

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    Yes they are all going to be 16:30:00

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Code:
    declare @dtime smalldatetime, @fdtime smalldatetime
    select @dtime = getdate()
    select @dtime
    select @fdtime = dateadd(mi,990,(convert(char(08),@dtime,112)))
    select @fdtime
    /*  OUTPUT
     
    ------------------------------------------------------ 
    2007-04-19 09:37:00
    (1 row(s) affected)
     
    ------------------------------------------------------ 
    2007-04-19 16:30:00
    (1 row(s) affected)
     
    */

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    Thank you.... I appreciate your time and help...
    Any recommendations for these functions in detail....
    I understand the 990... I guess it is brining back 00:00:00
    990 ---> 16:30:00
    112 <-- ???

    Jonathan

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Go read Books OnLine ... search on CONVERT ... it's a date format

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Jun 2006
    Location
    DC
    Posts
    123
    Thank you
    Much appreciated.....

Posting Permissions

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