SQL Statement BETWEEN 2 Dates - Advice Please.

[TomT]

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]]

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" <tomt@adslweb.co.uk> wrote in message
news:411a7a83$0$20243$cc9e4d1f@news-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
>
>

[TomT]

> 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

[Raymond D'Anjou \(raydan\)]

SELECT CAST('STARDATE 34523.2' AS DATETIME)

Works for me.... \\//

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:urZAxL%23fEHA.1196@TK2MSFTNGP11.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.)
>
>

[Aaron [SQL Server MVP]]

> 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\)]

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.xoc@dnartreb.noraa> wrote in message
news:e1pUhU%23fEHA.3272@TK2MSFTNGP11.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.)
>
>

[Aaron [SQL Server MVP]]

> 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.)

[Bob Barrows [MVP]]

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.xoc@dnartreb.noraa> wrote in message
> news:urZAxL%23fEHA.1196@TK2MSFTNGP11.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.

[TomT]

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

Beam me up Scotty !!



"Raymond D'Anjou (raydan)" <rdanjou@savantsoft.nonospamcom> wrote in message
news:eUFCia%23fEHA.3932@TK2MSFTNGP09.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.xoc@dnartreb.noraa> wrote in message
> news:e1pUhU%23fEHA.3272@TK2MSFTNGP11.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\)]

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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23Gy6Ab%23fEHA.3988@tk2msftngp13.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.xoc@dnartreb.noraa> wrote in message
> > news:urZAxL%23fEHA.1196@TK2MSFTNGP11.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]]

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.

[Raymond D'Anjou \(raydan\)]

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]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:%23hgToZHgEHA.3928@TK2MSFTNGP11.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.
>
>

[Aaron [SQL Server MVP]]

> 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\)]

LOL.

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

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:O%23dpAwHgEHA.704@TK2MSFTNGP09.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.)
>
>

[Bob Barrows [MVP]]

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.