Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    70

    Unanswered: updating date field

    Hey,

    How can i update a date field so that the date remains the same but the time will be set to midnight?
    I have fields where there is time that is non midnight and that field is used for grouping.

    Thanks,

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    update tab set datefield=dateadd(dd, datediff(dd,'20000101',datefield), '20000101')
    Maybe what you actually want is
    Code:
    select datefield=dateadd(dd, datediff(dd,'20000101',datefield), '20000101')
    ,sum(amount)
    from tab
    group by datefield=dateadd(dd, datediff(dd,'20000101',datefield), '20000101')

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd probably keep the detail in the column (in other words I wouldn't do the UPDATE) then I'd use DateAdd for the GROUP BY expression. That way you get to keep your detail, but you can also have the desired results too.

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or put the Whole Date value in a calculated column if performance is an issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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