Searching a datetime field (1 Viewer)

[Sun_Force]

I'm trying to write a sql to show all registered items on a specific date (let's say 2010/10/10):

1- tblTest is a linked table to SQL Server
2-Filtering field is RegisteredON. (Data type : datetime)

My first try:

SELECT * FROM tblTest WHERE [RegisteredON]=#2020/10/10#

No record is returned. Although I know I have several records with that date.

my Second try:

SELECT * FROM tblTest WHERE DateValue([RegisteredON])=#2020/10/10#

I receive a Data type mismatch in ceriteria expression Error

I know I can use the following but prefer not to (if there's another way)

SELECT * FROM tblTest WHERE [RegisteredON] BETWEEN #2020/10/09# AND #2020/10/11#

SELECT * FROM tblTest WHERE [RegisteredON]>=#2020/10/10 0:0:0# AND [RegisteredON]<=#2020/10/10 23:59:59#

Now my questions:
1- Why DateValue returns an error?
2- Is there any other way to filter a datetime field?

Thanks for any kind of advice

 

[MajP]

missing a # on the ends and the second is not a proper And. You need to repeat the field name

 

[Sun_Force]

Which one are you referring to. If the last one, sorry. it's a typo. I'll correct it.

 

[Isaac]

what if you change it to mm/dd/yyyy

also, this is probably because you have a time component, and need to really think of what the operands are doing - equals a date with no time? is that possible?

 

[Sun_Force]

what if you change it to mm/dd/yyyy

also, this is probably because you have a time component, and need to really think of what the operands are doing - equals a date with no time? is that possible?

Thanks for trying to help.

If I open the table the date format is yyyy/mm//dd,
So I don't think mm/dd/yyyy works in this situation.

I know about the rest. And I understand why the result is empty in first try. I just wanted to show the steps I've tried.
My questions were at the end of the post;
mostly why DateValue returns error and if there's any other way to filter a datetime.

 

[MajP]

Here is another one where I recommend using my CSQL for a properly formatted date field

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

 

[Isaac]

If I open the table the date format is yyyy/mm//dd,
So I don't think mm/dd/yyyy works in this situation.

That's because that's how sql server displays it, but access understands dates as #mm/dd/yyyy# regardless, IF you are coding this query in Access (not as a pass through, and not in t-sql).

Sure there's another way - use DateDiff against a whole date column, but that would be horrible performance. You need to specify the time component.

 

[Sun_Force]

Here is another one where I recommend using my CSQL for a properly formatted date field

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

Thanks.
Will check and report back.

 

[Sun_Force]

That's because that's how sql server displays it, but access understands dates as #mm/dd/yyyy# regardless, IF you are coding this query in Access (not as a pass through, and not in t-sql).

Sure there's another way - use DateDiff against a whole date column, but that would be horrible performance. You need to specify the time component.

Though performance is a main key in a data managing system, But since I'm at learning stage, it's good to know all my options.
Thanks for mentioning DateDiff. Will test it too.

 

[Minty]

@Isaac Access also understands yyyy-mm-dd , so it's a great way of knowing exactly what you are passing in regardless of the locale?

@Sun_Force I'm puzzled by your second example - in the immediate window

? DateValue(Now())= #2021-02-03#
True

This works ?

So that suggests that Access may not recognise your date field correctly. What server version and what data type is on the server?
If you are using DateTime2, certain access versions and ODBC drivers don't understand it as a date.
The fact it is displaying as yyyy/mm/dd also suggests that something is not right.

 

[Sun_Force]

@Isaac Access also understands yyyy-mm-dd , so it's a great way of knowing exactly what you are passing in regardless of the locale?

@Sun_Force I'm puzzled by your second example - in the immediate window

? DateValue(Now())= #2021-02-03#
True

This works ?

So that suggests that Access may not recognise your date field correctly. What server version and what data type is on the server?
If you are using DateTime2, certain access versions and ODBC drivers don't understand it as a date.
The fact it is displaying as yyyy/mm/dd also suggests that something is not right.

@Minty
?DateValue(Now())=#2021-02-04# in the immediate window returns True.

I checked the design of the table. The field's data type is datetime.
SQL Server : Microsoft SQL Server Enterprise (64bit)
Version : 13.0.4001.0
Language : English(US)
Collation : Japanese

We have two more servers in our university with Collation set to Korean and Chinese.
If you want I can check with those servers too.

This is a part of the table on sql server.

 

[Galaxiom]

I know I can use the following but prefer not to (if there's another way)

SELECT * FROM tblTest WHERE [RegisteredON] BETWEEN #2020/10/09# AND #2020/10/11#

The shortest code to write is often not the fastest code to process.
Always better to avoid applying a function to the values in the table before selecting.
Avoid using DateValue and DateDiff unnecessarily.
Stick to comparing the values in the table with a date range.

 

[Galaxiom]

? DateValue(Now())= #2021-02-03#
True

This works ?

So that suggests that Access may not recognise your date field correctly. What server version and what data type is on the server?
If you are using DateTime2, certain access versions and ODBC drivers don't understand it as a date.
The fact it is displaying as yyyy/mm/dd also suggests that something is not right.

There is a lot of misunderstanding and myths about date formats in Access.
In fact pretty much anything that can be interpreted as a date will be accepted by Access and its database engines. (JET and ACE).

In the query designer it will default to interpreting in the regional date format. In SQL it will default to mm/dd/yyyy. If you want a reliable interpretation best choose one that is unambiguous such as ISO or reliably use mm/dd/yyyy.

But anything that can be interpreted as a date in any recognisable format will be accepted and processed. Check out what it does with #30/2/21#.
TBH this promiscuous attitude is a pain in the arse.

SQL Server is a lot more discerning provided you talk directly to it.

 

[Sun_Force]

?DateValue(#30/2/21#) in immediate windows returns 1930/02/21

If I type the following in SQL window of a saved query and try to run it, I receive a Data Type Mismatch in Criteria expression error

SELECT * FROM tblTest WHERE DateValue([RegisteredON])=#30/2/21#

I'm still wondering why I'm receiving a data type mismatch on DateValue.

 

[Sun_Force]

Here is another one where I recommend using my CSQL for a properly formatted date field

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

@MajP

I followed your link. Immediate window shows this:

But still I receive an error in query design window:

 

[Galaxiom]

MajP's Csql() returns a string. It is intended for concatenation into an SQL command string.

It won't work as a date criteria in the designer.

 

[Sun_Force]

MajP's Csql() returns a string. It is intended for concatenation into an SQL command string.

It won't work as a date criteria in the designer.

@Galaxiom

Now I'm confused.
This image is from the link @MajP asked me to try.

Isn't it supposed to be used in a query?

 

[MajP]

Sorry, there are so many posts where people cannot properly delimit dates, strings, or null values. I got confused with another thread at the same time. More often in Access you are not hardwiring literals into a query, you are converting a form field or a date function. In your case this is not applicable because you are hardwiring a literal not converting a value.
One thing. SQL does not care what your regional format is, that has no impact. It uses American format MM/DD/YYYY

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

however to use in a query
CSql(dateserial(2021,02,02))
#02/02/2021#

CSQL(dateSerial(2021,02,02) + timeserial(23,59,59))
#02/02/2021 23:59:59#

 

[Pat Hartman]

There doesn't appear to be any reason to include time in this field. Therefore, do not use Now() to populate it. Use Date(). You can run a one time update query to strip the time off the date. Don't forget to back up the database first.

Sun, I don't know if your date field is indexed. If you are searching on it frequently, it should be. You should be aware that using the Format() function will prevent the database engine from using any index and it will force a full table scan which defeats the whole purpose of the index to begin with.

Either get rid of the time part of the data or use the between construct which is sargeable.

 

[Sun_Force]

There doesn't appear to be any reason to include time in this field. Therefore, do not use Now() to populate it. Use Date(). You can run a one time update query to strip the time off the date. Don't forget to back up the database first.

Sun, I don't know if your date field is indexed. If you are searching on it frequently, it should be. You should be aware that using the Format() function will prevent the database engine from using any index and it will force a full table scan which defeats the whole purpose of the index to begin with.

Either get rid of the time part of the data or use the between construct which is sargeable.

@Pat Hartman
Million thanks for the advice.
The datetime field is already indexed. But I'm not in a position to strip the time. It's actually an assignment from the collage and a lot of student share the same back end for their projects.