Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38

    Unanswered: how to search date range with two dates

    Hi Everybody

    I am using mssql 2000 and visual basic 6

    I am new to sql commands and statements, can somebody help me in correcting this query.

    Table field Information:
    r_chkindt datetime
    r_chkotdt datetime
    r_rooms
    r_type

    i want to input the fromdate and todate and filter the records for that date range., but i am not getting any results.

    i used query analyser to check the following sql query

    use cen

    declare @fromdate datetime
    declare @todate datetime

    select @fromdate= '2010-03-16'
    select @todate='2010-03-17'


    select r_refno,r_roomtype,r_chkindt,r_chkotdt,r_reqrooms from [hm-002a]
    where (r_chkindt>=@fromdate and r_chkindt<=@todate) or
    (r_chkotdt>=@fromdate and r_chkotdt<=@todate)


    i have records for this particular date range.

    can somebody point out the problem in my statement.

    I have been using clipper all these years and only now i am into visual basic and mss sql server.

    Thanks in advance.
    Last edited by richy_iii; 03-08-10 at 09:01. Reason: wanted the subject to be more clear
    Richy

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    i have records for this particular date range.

    can somebody point out the problem in my statement.
    Can you provide us with some sample data, the result you want to have and the result you get using the given SQL ?

    Without any warrant, this might be what you are looking for:
    Code:
    select r_refno, r_roomtype, r_chkindt, r_chkotdt, r_reqrooms 
    from [hm-002a]
    where (r_chkotdt >= @fromdate AND r_chkindt <= @todate)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by richy_iii View Post
    Hi Everybody

    I am using mssql 2000 and visual basic 6

    I am new to sql commands and statements, can somebody help me in correcting this query.

    Table field Information:
    r_chkindt datetime
    r_chkotdt datetime
    r_rooms
    r_type

    i want to input the fromdate and todate and filter the records for that date range., but i am not getting any results.

    i used query analyser to check the following sql query

    use cen

    declare @fromdate datetime
    declare @todate datetime

    select @fromdate= '2010-03-16'
    select @todate='2010-03-17'


    select r_refno,r_roomtype,r_chkindt,r_chkotdt,r_reqrooms from [hm-002a]
    where (r_chkindt>=@fromdate and r_chkindt<=@todate) or
    (r_chkotdt>=@fromdate and r_chkotdt<=@todate)


    i have records for this particular date range.

    can somebody point out the problem in my statement.

    I have been using clipper all these years and only now i am into visual basic and mss sql server.

    Thanks in advance.
    I used Clipper back in Summer '87, when I worked in Coney Island Hospital in Brooklyn, NY, I didn't realize it was even still around, wow.

  4. #4
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38
    Sample Data

    r_refno r_type r_chkindt r_chkotdt r_qty
    83.0 EXE 2010-03-03 00:00:00.000 2010-03-04 00:00:00.000 4.0
    84.0 SUITE 2010-03-03 00:00:00.000 2010-03-04 00:00:00.000 1.0
    85.0 EXE 2010-03-03 00:00:00.000 2010-03-04 00:00:00.000 5.0
    86.0 EXE 2010-03-03 00:00:00.000 2010-03-04 00:00:00.000 1.0
    87.0 CLUB 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 3.0
    87.0 EXE 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    87.0 SUITE 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    88.0 EXE 2010-04-08 00:00:00.000 2010-04-09 00:00:00.000 36.0
    92.0 EXE 2010-03-05 00:00:00.000 2010-03-06 00:00:00.000 1.0
    107.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    108.0 CLUB 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 1.0
    109.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    110.0 CLUB 2010-03-12 00:00:00.000 2010-03-14 00:00:00.000 1.0
    111.0 CLUB 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 1.0
    112.0 DLX 2010-03-15 00:00:00.000 2010-04-15 00:00:00.000 3.0
    113.0 DLX 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    114.0 EXE 2010-03-13 00:00:00.000 2010-03-14 00:00:00.000 1.0



    I want to filter records for the dates between 2010-03-08 (yyyy-MM-dd) and 2010-03-30 (yyyy-MM-dd)
    which falls inside r_chkindt and r_chkotdt dates.

    result should be
    r_refno r_type r_chkindt r_chkotdt r_qty
    87.0 CLUB 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 3.0
    87.0 EXE 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    87.0 SUITE 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    88.0 EXE 2010-04-08 00:00:00.000 2010-04-09 00:00:00.000 36.0
    107.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    108.0 CLUB 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 1.0
    109.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    110.0 CLUB 2010-03-12 00:00:00.000 2010-03-14 00:00:00.000 1.0
    111.0 CLUB 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 1.0
    112.0 DLX 2010-03-15 00:00:00.000 2010-04-15 00:00:00.000 3.0
    113.0 DLX 2010-03-12 00:00:00.000 2010-03-13 00:00:00.000 1.0
    114.0 EXE 2010-03-13 00:00:00.000 2010-03-14 00:00:00.000 1.0


    ie., r_refno=87,88,107,108,109,110,111,112,113,114 should be there
    Attached Files Attached Files

  5. #5
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38
    I also tried this following query

    declare @fromdate datetime
    declare @todate datetime

    select @fromdate= '2010-03-08'
    select @todate='2010-03-30'

    select r_refno,r_roomtype,r_chkindt,r_chkotdt,r_reqrooms from [hm-002a]
    where (@fromdate between r_chkindt and r_chkotdt ) or
    (@todate between r_chkindt and r_chkotdt ) and r_reqrooms<>0


    the results which i get is
    107.0 C SUITE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    107.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    107.0 DLX 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    107.0 EXE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    107.0 SUITE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    108.0 C SUITE 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 0.0
    108.0 CLUB 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 1.0
    108.0 DLX 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 0.0
    108.0 EXE 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 0.0
    108.0 SUITE 2010-03-08 00:00:00.000 2010-03-12 00:00:00.000 0.0
    109.0 C SUITE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    109.0 CLUB 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 1.0
    109.0 DLX 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    109.0 EXE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    109.0 SUITE 2010-03-08 00:00:00.000 2010-03-09 00:00:00.000 0.0
    111.0 C SUITE 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 0.0
    111.0 CLUB 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 1.0
    111.0 DLX 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 0.0
    111.0 EXE 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 0.0
    111.0 SUITE 2010-03-08 00:00:00.000 2010-03-10 00:00:00.000 0.0
    112.0 DLX 2010-03-15 00:00:00.000 2010-04-15 00:00:00.000 3.0
    Richy

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by richy_iii View Post
    Sample Data
    that's not sample data

    this is sample data --
    Code:
    CREATE TABLE [hm-002a] 
    ( r_refno  DECIMAL(4,1)
    , r_type   VARCHAR(9)
    , r_chkindt DATETIME
    , r_chkotdt DATETIME
    , r_qty DECIMAL(3,1)
    );
    INSERT INTO [hm-002a] VALUES ( 83.0,'EXE',  '2010-03-03','2010-03-04', 4.0)
    INSERT INTO [hm-002a] VALUES ( 84.0,'SUITE','2010-03-03','2010-03-04', 1.0)
    INSERT INTO [hm-002a] VALUES ( 85.0,'EXE',  '2010-03-03','2010-03-04', 5.0)
    INSERT INTO [hm-002a] VALUES ( 86.0,'EXE',  '2010-03-03','2010-03-04', 1.0)
    INSERT INTO [hm-002a] VALUES ( 87.0,'CLUB', '2010-03-12','2010-03-13', 3.0)
    INSERT INTO [hm-002a] VALUES ( 87.0,'EXE',  '2010-03-12','2010-03-13', 1.0)
    INSERT INTO [hm-002a] VALUES ( 87.0,'SUITE','2010-03-12','2010-03-13', 1.0)
    INSERT INTO [hm-002a] VALUES ( 88.0,'EXE',  '2010-04-08','2010-04-09',36.0)
    INSERT INTO [hm-002a] VALUES ( 92.0,'EXE',  '2010-03-05','2010-03-06', 1.0)
    INSERT INTO [hm-002a] VALUES (107.0,'CLUB', '2010-03-08','2010-03-09', 1.0)
    INSERT INTO [hm-002a] VALUES (108.0,'CLUB', '2010-03-08','2010-03-12', 1.0)
    INSERT INTO [hm-002a] VALUES (109.0,'CLUB', '2010-03-08','2010-03-09', 1.0)
    INSERT INTO [hm-002a] VALUES (110.0,'CLUB', '2010-03-12','2010-03-14', 1.0)
    INSERT INTO [hm-002a] VALUES (111.0,'CLUB', '2010-03-08','2010-03-10', 1.0)
    INSERT INTO [hm-002a] VALUES (112.0,'DLX',  '2010-03-15','2010-04-15', 3.0)
    INSERT INTO [hm-002a] VALUES (113.0,'DLX',  '2010-03-12','2010-03-13', 1.0)
    INSERT INTO [hm-002a] VALUES (114.0,'EXE',  '2010-03-13','2010-03-14', 1.0)
    Quote Originally Posted by richy_iii View Post
    r_refno=87,88,107,108,109,110,111,112,113,114 should be there
    no sir, that is not correct, 88 should ~not~ be there, as you can confirm by simple inspection -- 88 is for april, and you wanted a range in march

    i guess you did not try the solution posted by Wim, because it works --
    Code:
    DECLARE @fromdate DATETIME
    DECLARE @todate   DATETIME
    SELECT @fromdate = '2010-03-08'
    SELECT @todate   = '2010-03-30'
    
    SELECT r_refno
         , r_type
         , r_chkindt
         , r_chkotdt
         , r_qty 
      FROM [hm-002a]
     WHERE r_chkotdt >= @fromdate 
       AND r_chkindt <= @todate
    
    r_refno  r_type  r_chkindt  r_chkotdt  r_qty 
    -------- ------------------ ---------- ------
    87.0     CLUB    2010-03-12 2010-03-13 3.0
    87.0     EXE     2010-03-12 2010-03-13 1.0
    87.0     SUITE   2010-03-12 2010-03-13 1.0
    107.0    CLUB    2010-03-08 2010-03-09 1.0
    108.0    CLUB    2010-03-08 2010-03-12 1.0
    109.0    CLUB    2010-03-08 2010-03-09 1.0
    110.0    CLUB    2010-03-12 2010-03-14 1.0
    111.0    CLUB    2010-03-08 2010-03-10 1.0
    112.0    DLX     2010-03-15 2010-04-15 3.0
    113.0    DLX     2010-03-12 2010-03-13 1.0
    114.0    EXE     2010-03-13 2010-03-14 1.0
    
    11 row(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38
    Sorry I didnt know how to send sample data.
    I will try WIMs method.
    Richy

  8. #8
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38
    88 shouldnt be there. thanks!

  9. #9
    Join Date
    Jan 2004
    Location
    Coimbatore, India
    Posts
    38
    Thanks EveryBody

    Especially to Wim for his wonderful piece of code. It works!!!

    Thanks r937 for pointing out about the sample data.

    Richy

Posting Permissions

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