Odd and Differing Behaviour in Query on SQL data stores

[dynamictiger]

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:

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:

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:

WHERE (((Format(Left([StartTime],10),'mm\/dd\/yyyy'))>=#3/7/2019#)

I get nothing

Or

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

I get no records

Or

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

Still nada

And

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]

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]

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]

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

 

[dynamictiger]

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

Used format first. This didn't work.

 

[dynamictiger]

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]

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]

how about if you add additional '0' to your criteria, since now you are comparing it to a text:

WHERE (((Left([StartTime],10)) Between '2019-[COLOR="Red"]0[/COLOR]1-[COLOR="red"]0[/COLOR]1' And '2019-05-01'

 

[dynamictiger]

how about if you add additional '0' to your criteria, since now you are comparing it to a text:

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]

That makes no sense. How can characters be 'trimmed' from within a text string?

 

[arnelgp]

you already have leading 0 in your working query.

 

[Pat Hartman]

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.