Using wild card in DateTime

[jt]

Need help on defining a SQL query statement.

I have a field in my table defined as "Date/Time". This is in Access.

The data looks like this in the field, "4/19/2004 8:59:17 AM" when entered.

When I run the sql query statement with date and time like this, works well.
SELECT * FROM [Florida] WHERE Call_Date = #4/19/2004 8:59:17 AM#

I want to query the DateTime field by date only and I don't care about the
time.

If I run it by this,
SELECT * FROM [Florida] WHERE Call_Date = #4/19/2004 ??:??:?? AM#

Error: Syntax error in date in query expression 'Call_Date = #4/19/2004
??:??:?? AM#'.

SQL : SELECT * FROM [Florida] WHERE Call_Date = #4/19/2004 ??:??:?? AM#


Can you give me any ideas what I am doing wrong?

Appreciate any help.

Thanks,
jt

[Bob Barrows]

jt wrote:

> Need help on defining a SQL query statement.
>
> I have a field in my table defined as "Date/Time". This is in Access.
>
> The data looks like this in the field, "4/19/2004 8:59:17 AM" when
> entered.
>
> When I run the sql query statement with date and time like this,
> works well. SELECT * FROM [Florida] WHERE Call_Date = #4/19/2004
> 8:59:17 AM#
>
> I want to query the DateTime field by date only and I don't care
> about the time.
>

WHERE Call_Date >= #2004-04-19# AND Call_Date < DateAdd("d",1,#2004-04-19#)

HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Evertjan.]

Bob Barrows wrote on 22 apr 2004 in microsoft.public.inetserver.asp.db:

>> I want to query the DateTime field by date only and I don't care
>> about the time.
>>

>
> WHERE Call_Date >= #2004-04-19# AND Call_Date <
> DateAdd("d",1,#2004-04-19#)
>

WHERE datediff("d", Call_Date, #2004-04-19#) = 0

not tested, please be careful

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> Bob Barrows wrote on 22 apr 2004 in
> microsoft.public.inetserver.asp.db:
>

>>> I want to query the DateTime field by date only and I don't care
>>> about the time.
>>>

>>
>> WHERE Call_Date >= #2004-04-19# AND Call_Date <
>> DateAdd("d",1,#2004-04-19#)
>>

>
> WHERE datediff("d", Call_Date, #2004-04-19#) = 0
>

If you have an index on Call_Date, you should not use this technique because
it is non-sargable. Avoid using a function on a column in your WHERE clause
if at all possible because doing so forces a table scan even if an index
exists.

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.

[Evertjan.]

Bob Barrows [MVP] wrote on 22 apr 2004 in
microsoft.public.inetserver.asp.db:

> Evertjan. wrote:

>> Bob Barrows wrote on 22 apr 2004 in
>> microsoft.public.inetserver.asp.db:
>>

>>>> I want to query the DateTime field by date only and I don't care
>>>> about the time.
>>>>
>>>
>>> WHERE Call_Date >= #2004-04-19# AND Call_Date <
>>> DateAdd("d",1,#2004-04-19#)
>>>

>>
>> WHERE datediff("d", Call_Date, #2004-04-19#) = 0
>>

> If you have an index on Call_Date, you should not use this technique
> because it is non-sargable. Avoid using a function on a column in your
> WHERE clause if at all possible because doing so forces a table scan
> even if an index exists.
>

Right. forgot about that.

If the above is used frequently, wouldn't it be better to add a field
containing just the date?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

> Bob Barrows [MVP] wrote on 22 apr 2004 in
> microsoft.public.inetserver.asp.db:
>

>> Evertjan. wrote:

>>> Bob Barrows wrote on 22 apr 2004 in
>>> microsoft.public.inetserver.asp.db:
>>>
>>>>> I want to query the DateTime field by date only and I don't care
>>>>> about the time.
>>>>>
>>>>
>>>> WHERE Call_Date >= #2004-04-19# AND Call_Date <
>>>> DateAdd("d",1,#2004-04-19#)
>>>>
>>>
>>> WHERE datediff("d", Call_Date, #2004-04-19#) = 0
>>>

>> If you have an index on Call_Date, you should not use this technique
>> because it is non-sargable. Avoid using a function on a column in
>> your WHERE clause if at all possible because doing so forces a table
>> scan even if an index exists.
>>

>
> Right. forgot about that.
>
> If the above is used frequently, wouldn't it be better to add a field
> containing just the date?

Not really, considering the overhead that would be required to maintain this
field, especially considering that it won't gain you much in the way of
performance if you already have an index on the Call_Date field. The >=, <
criterion I suggested is sargable and will use the index on Call_Date if it
exists.

I have no problem with denormalizing a table if it has a substantial impact
on query performance, enough to offset the increase in overhead required to
maintain the denormalized data. In this case however, I do not think it is
warranted.

For example, if you had a frequently run query (or better yet, set of
queries) that required all the records from a particular month (ignoring the
year), then I would see a reason to break out the month into its own indexed
field. There is no way to create a sargable criterion that will filter
records for a specified month disregarding of the year.

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.