Querying Times

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a SQL Sever table of data type datetime. I enter a time in
this field by keying 8AM and tabing to the next field. When I try to query
the table with the criteria of #8:00:00 AM# it returns no records. What is
the correct way to query times? Including SQL for VBA.
 
Hi, Matt

The 0 data for Sql Server is 01/01/1900, the 0 data for Access is
12/30/1899.
Access put in the datetime field the value '12/30/1899 08:00:00'

When execute a query with the criteria #8:00:00 AM#
Sql Server receive this command
----
SELECT FieldDate
FROM MyTable
WHERE (FieldDate= {t '08:00:00'} )
----
is equal to
----
SELECT FieldDate
FROM MyTable
WHERE (FieldDate= '01/01/1900 08:00:00')
----
and returns no records


For solve use this workaround.
(Access query)
 
Thank you Ciao, I did get this to work but don't totally understand why. Can
you explain the |FieldDate+1| and why the date used is 12/31/1899 (one less
than 01/01/1900). Much appreciated.

Matt
 
Matt Sonic said:
Thank you Ciao, I did get this to work but don't totally understand why. Can
you explain the |FieldDate+1| and why the date used is 12/31/1899 (one less
than 01/01/1900). Much appreciated.
Click to expand...

Hi, Matt

Ok , the problem is that this ODBC function
----
{t '08:00:00'}
----

In Sql Server is equal to the current date + the part of the hour
(Transact SQL language)
----
SELECT {t '08:00:00'}

Result
08/11/2005 08:00:00.000
----

the record does not come found because it Hour is memorized so
----
12/30/1899 08:00:00.000
----

if I seek the hour in this manner
----
SELECT *
FROM MyTable
WHERE FieldDate=#12/30/1899 08:00:00#
----
or in this manner
----
SELECT *
FROM MyTable
WHERE FieldDate=#08:00:00#
----

the ODBC driver translates in this manner
----
SELECT *
FROM MyTable
WHERE FieldDate = {t '08:00:00'}
----
and the record does not come found
(In my opinion this way to translate is a bug)


otherwise if I seek the hour in this manner
----
SELECT *
FROM MyTable
WHERE FieldDate+1=#12/31/1899 08:00:00#
----

the ODBC driver translates in this manner
----
SELECT *
FROM MyTable
WHERE FieldDate +1 = {ts '1899-12-31 08:00:00'}
----

in the table the value of FieldDate is equal to 12/30/1899 08:00:00
+1=12/31/1899 08:00:00
therefore the equality with {ts '1899-12-31 08:00:00'} is checked true

I hope of not to have created confusion :-)

bye
Giorgio
 
Back
Top