Odd and Differing Behaviour in Query on SQL data stores (1 Viewer)

dynamictiger

Registered User.
Local time
Today, 02:24
Joined
Feb 3, 2002
Messages
270
Thanks to everyone for looking into this. It really has me baffled.

I have two online SQL databases I am linking to in Access in order to produce multiple reports, queries and so on.

Both databases contain dates and typical for SQL the date on the linked table is formatted dd-mm-yyyy hh:nn:ss.

I am running two different queries. The first finds chemical results and limits the records by date (this is from one of the SQL linked tables). The relevant section of the query is:

Code:
Left([LogTime],10)) Between '2019-01-01' And '2019-01-10')


In short I am trimming the time from the date and leaving just the date then query with yyyy-mm-dd. This works and reliably works.


On another query I have a similar requirement. I am wanting to return time records between two specific dates. If I add:
Code:
WHERE (((Left([StartTime],10)) Between '2019-1-1' And '2019-05-01'

This query returns nothing at all. Yet the underlying data looks identical in every way???

If I change the query to:
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>=#3/7/2019#)
I get nothing

Or
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>=7/3/2019));
I get no records

Or
Code:
WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>='7/3/2019'));
Still nada

And
Code:
WHERE (((Left([StartTime],10))>=3-7-2019));
Appears to be ignoring the criteria regardless of which way I configure the date.


I understand dates can be confusing, but this is past confusing and becoming threatening to my remaining hair follicles. I am really flumoxed and am starting to wonder about alternative approaches. I was hoping someone has come across an equally baffling situation and may give me a better pointer than here is a link to so and so website. I promise I have tried it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:24
Joined
May 7, 2009
Messages
19,169
its not odd at all. you are only comparing the time against the date. if you cut the date part it will be 0 date or dec 30 1899.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:24
Joined
Feb 28, 2001
Messages
27,001
I looked this up because I wasn't familiar with SQL server internals. But it appears that date/time variables are numeric for SQL server similar to (if not identical to) Access internal date formats. When you say "SQL databases" did you mean SQL Server or some other SQL back end?

So... what is the data type of [StartTime]? You said the BE files contained dates, but I didn't think that you could use the LEFT function on dates because if they are truly date/time variables, they are numeric.

If on the other hand [StartTime] is text-oriented, then Access will do a string comparison because your dates are shown as quoted strings without the appropriate syntax (an octothorpe #) for enclosing literal dates.

Every comparison I saw looked like an apples-to-oranges comparison. Therefore I am not surprised to see that you are getting trouble from that query.
 

June7

AWF VIP
Local time
Yesterday, 18:24
Joined
Mar 9, 2014
Messages
5,423
Can use string manipulation functions on date values. Access will do implicit conversion. However, result may not be acceptable if not first formatted to a consistent length string. LEFT should be unnecessary as FORMAT function can do the extraction.

Also, review http://allenbrowne.com/ser-36.html
 
Last edited:

dynamictiger

Registered User.
Local time
Today, 02:24
Joined
Feb 3, 2002
Messages
270
its not odd at all. you are only comparing the time against the date. if you cut the date part it will be 0 date or dec 30 1899.

Have converted to date in every conceivable way I can think of
 

dynamictiger

Registered User.
Local time
Today, 02:24
Joined
Feb 3, 2002
Messages
270
When you say "SQL databases" did you mean SQL Server or some other SQL back end?

Yes I have two backend SQL servers at the moment. We are upgrading the online system so currently are running part old (online tools) and part new. Eventually it will all transfer to the new system, but the new code is taking longer than anticipated.

So... what is the data type of [StartTime]? You said the BE files contained dates, but I didn't think that you could use the LEFT function on dates because if they are truly date/time variables, they are numeric.

If on the other hand [StartTime] is text-oriented, then Access will do a string comparison because your dates are shown as quoted strings without the appropriate syntax (an octothorpe #) for enclosing literal dates.

The linked table is showing the field as short text. It does suggest it is a string, comparison, and certainly works with the new SQL like that. The old even though stored the same refuses to work with string compare tools. This is my main cause of confusion.


I can literally snip the SQL statement from one query that works, change the field names and drop it in aa query on the different table and it refuses to work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:24
Joined
May 7, 2009
Messages
19,169
how about if you add additional '0' to your criteria, since now you are comparing it to a text:
Code:
WHERE (((Left([StartTime],10)) Between '2019-[COLOR="Red"]0[/COLOR]1-[COLOR="red"]0[/COLOR]1' And '2019-05-01'
 

dynamictiger

Registered User.
Local time
Today, 02:24
Joined
Feb 3, 2002
Messages
270
how about if you add additional '0' to your criteria, since now you are comparing it to a text:
Code:
WHERE (((Left([StartTime],10)) Between '2019-[COLOR="Red"]0[/COLOR]1-[COLOR="red"]0[/COLOR]1' And '2019-05-01'


I tried that and it trims the leading 0 every time...
 

June7

AWF VIP
Local time
Yesterday, 18:24
Joined
Mar 9, 2014
Messages
5,423
That makes no sense. How can characters be 'trimmed' from within a text string?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:24
Joined
May 7, 2009
Messages
19,169
you already have leading 0 in your working query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:24
Joined
Feb 19, 2002
Messages
42,981
The Left() function is converting the date to a string so it must be formatted as yyyy-mm-dd to work correctly and that means including leading zeros for 1 digit months and days.
 

Users who are viewing this thread

Top Bottom