Results 1 to 10 of 10

Thread: Date Query

  1. #1
    Join Date
    Apr 2004
    Location
    Melbourne
    Posts
    144

    Unanswered: Date Query

    how would i query the table with dateid for a particular date

    DateID
    2005-11-24 14:40:00
    select * from tbl1 where dateid like '2005-11-24%' ?

    how about date and time like 9-11am of something

  2. #2
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Ummm.. How about


    select * from tbl1 where dateid >= '2005-11-24 09:00:00' and dateid < '2005-11-24 12:00:00'

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    For the date only, you can:
    SELECT * FROM tbl1 WHERE CONVERT(varchar(10), DateID, 101) = '2005-11-24'

    For the date/time range, you would just use BETWEEN, as in:
    SELECT * FROM tbl1 WHERE DateID BETWEEN '2005-11-24 09:00:00' AND '2005-11-24 11:00:00' (or use bmalar's suggestion above).
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd like to see just how that BETWEEN works. I'm thinking at at the very least you'll need to decorate the DateID a bit.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I dunno 'bout all that...

    I didn't have anything to get too fancy with testing, but this
    Code:
    SELECT * FROM currentportfolio 
    WHERE createdate 
    BETWEEN '2005-11-24 23:59:59' AND '2005-11-25 00:00:01'
    managed to find all the rows in my DB with a smalldatetime that was '2005-11-25 00:00:00' (actually inserted by doing the ole' faithful
    Code:
    CONVERT(varchar(10), GETDATE(), 101)
    and this:
    Code:
    SELECT * 
    FROM eventlog 
    WHERE date BETWEEN '2005-11-25 13:55:02' AND '2005-11-25 13:55:03'
    order by date
    managed to find everything in my table with a datetime within the two seconds indicated (9 rows)

    whatchew talkin' 'bout, Willis? What means "decorate"?
    Last edited by TallCowboy0614; 11-25-05 at 20:21.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I must have been doing drugs...

    The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression... I wanted to see just how that would work!

    -PatP

  7. #7
    Join Date
    Dec 2003
    Posts
    39
    do u mean just specify date without time or time without date? i usually use datediff and datepart

    Nov 24, 2005
    select * from tbl1 where datediff(day,dateid,'20051124')=0

    09:00 - 11.00
    select * from tbl1 where (datepart(hour,dateid) between 9 and 10) or (datepart(hour,dateid)=11 and datepart(minute,dateid)=0)
    Link Link

  8. #8
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Pat Phelan
    The thought that was running through my head was that you were mixing BETWEEN and LIKE in the same expression...
    That's how the today's kids write SQL:
    Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.

  9. #9
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by ivon
    That's how the today's kids write SQL:
    Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.
    ROTFLMAO

    Like, um, okay dude...
    Have you hugged your backup today?

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by ivon
    That's how the today's kids write SQL:
    Where DateID is like, between '12-11-2005' and, you know, 'later' and stuff, dude.
    What scares me about that is I read it and it sounded like perfect English to me But I blame my two high-school aged daughters for that...the older boys, thank goodness, graduated without ever uttering the word "like" in a sentence in that way...
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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