Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Jul 2007
    Posts
    25

    Unanswered: Rond Time Down to Hour

    I want to round times down to the previous hour,
    For example a call time of 7/31/07 11:51:13.532 would become 7/31/07 11:00:00.000.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    DECLARE @d		DATETIME
    SET @d = '7/31/07 11:51:13.532'
    
    SELECT @d, Convert(DATETIME, Convert(CHAR(14), @d, 121) + '00')
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    arithmetic is more efficient than string manipulation and conversion

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '7/31/07 11:51:13.532'), 0)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2007
    Posts
    25
    Thanks
    I did it with r937 User

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    arithmetic is more efficient than string manipulation and conversion

    SELECT DATEADD(hh, DATEDIFF(hh, 0, '7/31/07 11:51:13.532'), 0)
    This is an excellent example of why nonstandard, undocumented, and unsupported tricks provide useful solutions and should be used instead of possibly slower but supportable solutions!

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    non-standard? relevance of the standards to this problem, please?

    because standard sql's date functionality is not what you want to use in microsoft sql server

    undocumented? pshhhhh, you've misplaced your copy of BOL again, eh?

    unsupported??!!! i dunno what you been smokin, but i want some...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Undocumented because I can't find anything that even hints at the possibility that "adding zero will cause truncation" anywhere in BOL or on the web.

    Unsupported because Microsoft PSS has explicitly stated that this behavior of DateAdd() is undocumented, unsupported, and unreliable. It is subject to change at their whim, expected to change, and is emphatically not guaranteed to work.

    I'll conceed your point on non-standard because there isn't any formal standard for date manipulation in SQL.

    I still maintain that conversion to the ISO character form and manipulating that character form is more portable and easier to understand than relying on obscure behaviors of vendor specific functions that even the vendor's own support group gets nervous about!

    -patP

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Pat Phelan
    Undocumented because I can't find anything that even hints at the possibility that "adding zero will cause truncation" anywhere in BOL or on the web.
    Who is adding zero?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you sure Pat? I don't there is any trickery here. The inner function works out how many hours have passed from "zero" date (and this could also be any genuine date so long as it is also used in the outer equation) to the supplied date. The outer equation then adds that number of hours to the "zero" date. If that isn't supported then I worry about any use of dateadd\ datediff.

    It's all maffumatical innit?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Unsupported because Microsoft PSS has explicitly stated that this behavior of DateAdd() is undocumented, unsupported, and unreliable.
    citation, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    citation, please
    You know full well that the contents of an SR are private. If I knew of a publically available source I would have provided that in my original posting.

    -PatP

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    You know full well that the contents of an SR are private.
    bulls***

    i don;t even know what an "SR" is

    nor a "Microsoft PSS" for that matter



    okay, "bull***" is perhaps too strong a word

    at the very least, it is quite presumptuous of you to suggest that you know what i know


    and quit trying to change the subject!!! we're talking about DATEADD, not the extent of my knowledge of TLAs
    Last edited by gvee; 08-06-07 at 14:33. Reason: clean up of language
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by pootle flump
    Are you sure Pat? I don't there is any trickery here.
    The problem lies in treating zero (an integer) as though it were a date. As type checking beomes available in future releases of T-SQL, this behavior is presumed to fail. Although I haven't been privvy to discussions about such things lately, there was also consideration for changing the "zero" date value if the julian to gregorian conversion issues could be settled (to allow references to dates much earlier than the present version of SQL Server allows).

    While Rudy's trick works (at least for now), I see it as very poor practice. It relies on being able to subvert type conversion which is problematic, it relies on undocumented behaviors related to that subversion, and it is difficult for new users to understand (at least in my experience).

    -PatP

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, point made about using "0" as the zero date

    does this make you happier? let's use hiroshima day...

    SELECT DATEADD(hh, DATEDIFF(hh, '1945-08-06', '7/31/07 11:51:13.532'), '1945-08-06')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just catching up...
    Are you simply using a constant instead of the zero date?
    George
    Home | Blog

Posting Permissions

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