Results 1 to 5 of 5

Thread: datetime help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: datetime help

    i have a table called users with a field called date

    how can i select all users where the date is today and the hour is between 8 and 9?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select id
      from users
     where date >= dateadd(hh,8,dateadd(d,datediff(d,'1949-09-09',getdate()),'1949-09-09')
       and date  < dateadd(hh,9,dateadd(d,datediff(d,'1949-09-09',getdate()),'1949-09-09')
    p.s. if you're wondering what 1949-09-09 is, dat's my boifday
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by r937
    Code:
    select id
      from users
     where date >= dateadd(hh,8,dateadd(d,datediff(d,'1949-09-09',getdate()),'1949-09-09')
       and date  < dateadd(hh,9,dateadd(d,datediff(d,'1949-09-09',getdate()),'1949-09-09')
    p.s. if you're wondering what 1949-09-09 is, dat's my boifday
    Rudy, I'm pretty sure I've read it somewhere but I can't seem to find the link to that article that someone posted on this forums, or remember the exact reasons. My question is: why are you using >= and < instead of between? Is there any performance advantage with your approach? Is it just personal preference?

    Sorry for appending this question in this thread but I'm asking this because I tend to use BETWEEN a lot

    Thanks in advance,
    Best regards

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    BETWEEN includes both endpoints, which means that you have to give a value for the upper one

    if you want everything between 8 o'clock and 9 o'clock, you don't actually want to include 09:00:00.000, right?

    performance is the same

    accuracy is more important anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by r937
    BETWEEN includes both endpoints, which means that you have to give a value for the upper one

    if you want everything between 8 o'clock and 9 o'clock, you don't actually want to include 09:00:00.000, right?

    performance is the same

    accuracy is more important anyway
    Indeed, it is more accurate that way, unless you hammer down the upper value and then use the between. Thanks for the enlightment
    Last edited by Diabolic; 01-13-08 at 09:37.

Posting Permissions

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