Professional Web Applications Themes

SQL Statement BETWEEN 2 Dates - Advice Please. - ASP Database

strList = "select * " _ & "from tbltest "_ & "WHERE user LIKE '%" & Replace(strSearch, "'", "''") & "%' AND ID < '2' "_ & "AND IDate Between #01/08/2004# AND #09/08/2004# " _ & "order by CDATE(IDate)" This works but it shows all dates not just the ones between the 2 dates... The access MDB has the 'IDate' formated as Date/Time. How do I correct this ?? Thanks...

  1. #1

    Default SQL Statement BETWEEN 2 Dates - Advice Please.

    strList = "select * " _
    & "from tbltest "_
    & "WHERE user LIKE '%" & Replace(strSearch, "'", "''") & "%' AND ID < '2'
    "_
    & "AND IDate Between #01/08/2004# AND #09/08/2004# " _
    & "order by CDATE(IDate)"

    This works but it shows all dates not just the ones between the 2 dates...

    The access MDB has the 'IDate' formated as Date/Time.

    How do I correct this ??

    Thanks


    TomT Guest

  2. #2

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    Stop using ambiguous date formats! I can't tell if you mean January 8th or
    August 1st, how do you expect the software to know?

    WHERE IDate >= #2004-08-01# AND IDate < #2004-08-10#

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)




    "TomT" <tomtadslweb.co.uk> wrote in message
    news:411a7a83$0$20243$cc9e4d1fnews-text.dial.pipex.com...
    > strList = "select * " _
    > & "from tbltest "_
    > & "WHERE user LIKE '%" & Replace(strSearch, "'", "''") & "%' AND ID <
    '2'
    > "_
    > & "AND IDate Between #01/08/2004# AND #09/08/2004# " _
    > & "order by CDATE(IDate)"
    >
    > This works but it shows all dates not just the ones between the 2
    dates...
    >
    > The access MDB has the 'IDate' formated as Date/Time.
    >
    > How do I correct this ??
    >
    > Thanks
    >
    >

    Aaron [SQL Server MVP] Guest

  3. #3

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    > Stop using ambiguous date formats! I can't tell if you mean January 8th
    or
    > August 1st, how do you expect the software to know?
    >
    > WHERE IDate >= #2004-08-01# AND IDate < #2004-08-10#
    Hi,

    I thought 01/08/2004 was a valid date format ??

    Anyway... Changing the SQL Statement to the below and now it seems to be
    working !!

    WHERE IDate >= #2004-08-01# AND IDate <= #2004-08-10#

    Cheers


    TomT Guest

  4. #4

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    SELECT CAST('STARDATE 34523.2' AS DATETIME)

    Works for me.... \\//

    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:urZAxL%23fEHA.1196TK2MSFTNGP11.phx.gbl...
    > > I thought 01/08/2004 was a valid date format ??
    >
    > Depends on what country you are in, what country the server is in, what
    > language/regional settings have been applied on the server, the
    > language/locale of the application (in this case Access), the application
    > code, and the user.
    >
    > With YYYY-MM-DD, nobody can be confused, unless they are expecting a
    > stardate.
    >
    > (With SQL Server, you use YYYYMMDD, not YYYY-MM-DD.)
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >

    Raymond D'Anjou \(raydan\) Guest

  5. #5

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    > I thought 01/08/2004 was a valid date format ??

    Depends on what country you are in, what country the server is in, what
    language/regional settings have been applied on the server, the
    language/locale of the application (in this case Access), the application
    code, and the user.

    With YYYY-MM-DD, nobody can be confused, unless they are expecting a
    stardate.

    (With SQL Server, you use YYYYMMDD, not YYYY-MM-DD.)

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  6. #6

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    Just changing the statement to:
    SELECT CAST(34523.2 AS DATETIME)
    does return a date.

    Do you think that the old StarTrek show used SQL to generate star dates???

    How did you like my \\//
    It's suppose to represent Spock's hand greeting.

    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:e1pUhU%23fEHA.3272TK2MSFTNGP11.phx.gbl...
    > > SELECT CAST('STARDATE 34523.2' AS DATETIME)
    > >
    > > Works for me.... \\//
    >
    > Wow, you must have been given advance access to the Spock patch. It was
    > promised to me, but I haven't seen it yet. ;-)
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >

    Raymond D'Anjou \(raydan\) Guest

  7. #7

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    > SELECT CAST('STARDATE 34523.2' AS DATETIME)
    >
    > Works for me.... \\//
    Wow, you must have been given advance access to the Spock patch. It was
    promised to me, but I haven't seen it yet. ;-)

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  8. #8

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    1. That has nothing to do with Access, which does not support CAST
    2. It has nothing to do with the point that Aaron was making

    Raymond D'Anjou (raydan) wrote:
    > SELECT CAST('STARDATE 34523.2' AS DATETIME)
    >
    > Works for me.... \\//
    >
    > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > news:urZAxL%23fEHA.1196TK2MSFTNGP11.phx.gbl...
    >>> I thought 01/08/2004 was a valid date format ??
    >>
    >> Depends on what country you are in, what country the server is in,
    >> what language/regional settings have been applied on the server, the
    >> language/locale of the application (in this case Access), the
    >> application code, and the user.
    >>
    >> With YYYY-MM-DD, nobody can be confused, unless they are expecting a
    >> stardate.
    >>
    >> (With SQL Server, you use YYYYMMDD, not YYYY-MM-DD.)
    >>
    >> --
    >> [url]http://www.aspfaq.com/[/url]
    >> (Reverse address to reply.)
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  9. #9

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    Well thats confused the hell out of me... :)

    Beam me up Scotty !!



    "Raymond D'Anjou (raydan)" <rdanjousavantsoft.nonospamcom> wrote in message
    news:eUFCia%23fEHA.3932TK2MSFTNGP09.phx.gbl...
    > Just changing the statement to:
    > SELECT CAST(34523.2 AS DATETIME)
    > does return a date.
    >
    > Do you think that the old StarTrek show used SQL to generate star dates???
    >
    > How did you like my \\//
    > It's suppose to represent Spock's hand greeting.
    >
    > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > news:e1pUhU%23fEHA.3272TK2MSFTNGP11.phx.gbl...
    > > > SELECT CAST('STARDATE 34523.2' AS DATETIME)
    > > >
    > > > Works for me.... \\//
    > >
    > > Wow, you must have been given advance access to the Spock patch. It was
    > > promised to me, but I haven't seen it yet. ;-)
    > >
    > > --
    > > [url]http://www.aspfaq.com/[/url]
    > > (Reverse address to reply.)
    > >
    > >
    >
    >

    TomT Guest

  10. #10

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    True.
    I subscribe to sqlserver.programming too, don't know much Access so I can't
    really tell what doesn't work.
    Humble appologies.
    Besides that, I was just having a bit of fun and forgot to put a :-) at the
    end of the post.

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:%23Gy6Ab%23fEHA.3988tk2msftngp13.phx.gbl...
    > 1. That has nothing to do with Access, which does not support CAST
    > 2. It has nothing to do with the point that Aaron was making
    >
    > Raymond D'Anjou (raydan) wrote:
    > > SELECT CAST('STARDATE 34523.2' AS DATETIME)
    > >
    > > Works for me.... \\//
    > >
    > > "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    > > news:urZAxL%23fEHA.1196TK2MSFTNGP11.phx.gbl...
    > >>> I thought 01/08/2004 was a valid date format ??
    > >>
    > >> Depends on what country you are in, what country the server is in,
    > >> what language/regional settings have been applied on the server, the
    > >> language/locale of the application (in this case Access), the
    > >> application code, and the user.
    > >>
    > >> With YYYY-MM-DD, nobody can be confused, unless they are expecting a
    > >> stardate.
    > >>
    > >> (With SQL Server, you use YYYYMMDD, not YYYY-MM-DD.)
    > >>
    > >> --
    > >> [url]http://www.aspfaq.com/[/url]
    > >> (Reverse address to reply.)
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >

    Raymond D'Anjou \(raydan\) Guest

  11. #11

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    Raymond D'Anjou (raydan) wrote:
    > True.
    > I subscribe to sqlserver.programming too, don't know much Access so I
    > can't really tell what doesn't work.
    > Humble appologies.
    > Besides that, I was just having a bit of fun and forgot to put a :-)
    > at the end of the post.
    Yeah, I missed that - I thought there was a typo. Need to adjust my humor
    index :-)

    Bob
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

  12. #12

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    I was starting to worry about you Bob.
    Must be that [MVP] title.
    When you get that it's "No more fun and games". ;-)

    "Bob Barrows [MVP]" <reb01501NOyahoo.SPAMcom> wrote in message
    news:%23hgToZHgEHA.3928TK2MSFTNGP11.phx.gbl...
    > Yeah, I missed that - I thought there was a typo. Need to adjust my humor
    > index :-)
    >
    > Bob
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >

    Raymond D'Anjou \(raydan\) Guest

  13. #13

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    > Yeah, I missed that - I thought there was a typo. Need to adjust my humor
    > index :-)
    I think this will fix the problem:

    DROP INDEX Bob.ix_Humor
    GO

    CREATE /* important part here that was missing -> */
    CLUSTERED UNIQUE /* end important part */
    INDEX ix_Humor ON Bob(Humor, Sarcasm, Wit)
    GO

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)


    Aaron [SQL Server MVP] Guest

  14. #14

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    LOL.

    I guess Aaron is out to prove that he has it and Bob don't.

    "Aaron [SQL Server MVP]" <ten.xocdnartreb.noraa> wrote in message
    news:O%23dpAwHgEHA.704TK2MSFTNGP09.phx.gbl...
    > > Yeah, I missed that - I thought there was a typo. Need to adjust my
    humor
    > > index :-)
    >
    > I think this will fix the problem:
    >
    > DROP INDEX Bob.ix_Humor
    > GO
    >
    > CREATE /* important part here that was missing -> */
    > CLUSTERED UNIQUE /* end important part */
    > INDEX ix_Humor ON Bob(Humor, Sarcasm, Wit)
    > GO
    >
    > --
    > [url]http://www.aspfaq.com/[/url]
    > (Reverse address to reply.)
    >
    >

    Raymond D'Anjou \(raydan\) Guest

  15. #15

    Default Re: SQL Statement BETWEEN 2 Dates - Advice Please.

    Aaron [SQL Server MVP] wrote:
    >> Yeah, I missed that - I thought there was a typo. Need to adjust my
    >> humor index :-)
    >
    > I think this will fix the problem:
    >
    > DROP INDEX Bob.ix_Humor
    > GO
    >
    > CREATE /* important part here that was missing -> */
    > CLUSTERED UNIQUE /* end important part */
    > INDEX ix_Humor ON Bob(Humor, Sarcasm, Wit)
    > GO
    Don't forget the fillfactor. ;-)

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Barrows [MVP] Guest

Similar Threads

  1. ASP/VBS Dates Between Dates
    By Sanjay in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 28th, 06:34 PM
  2. Dates again
    By Clive Moss in forum ASP Database
    Replies: 4
    Last Post: January 14th, 03:35 AM
  3. Replies: 4
    Last Post: October 13th, 02:57 PM
  4. Need an advice on 'declare cursor' statement
    By gene in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 10th, 08:56 PM
  5. Help with Dates please
    By Ian Piper in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 07:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139