Access query for tables in linked SQL Server DB not returning data for search of datetime values as times

Question

Here's the query that is being executed on SQL Server 2008:

SELECT "dbo"."tblMainData"."recID" 
FROM "dbo"."tblMainData" 
WHERE ("timeInMain" BETWEEN {t '08:00:00'} AND {t '17:00:00'} )

This query is generated by a Microsoft Access 2007 database that has linked tables in the SQL Server 2008 database.

Here is the Access query that generates the above query:

SELECT tblMainData.timeInMain
FROM tblMainData
WHERE (((tblMainData.timeInMain) Between #12/30/1899 8:0:0# And #12/30/1899 17:0:0#));

Anyway, I know that there is data that meets this criteria. Why aren't I seeing the expected results?

This is the first time I have seen the {t '08:00:00'} syntax, which I'm assuming is supposed to disregard the date portion of the datetime field. I couldn't find any documentation on it.

Thanks for any help.

UPDATE

Just ran select {t '08:00:00'} in SSMS, which returned 2010-11-17 08:00:00.000. That explains why I'm not getting data back. But, as you can see, the Access query is explicit about which date to use and the generated TSQL doesn't include it. What to do?

UPDATE 2

I have done some experimenting. Access only removes the date from generated TSQL when it is 12/30/1899. Apparently, this is a special date that access uses when a user stores only a time in a DateTime field and Access assumes that if it's included in a query, then it's not really important. Wow.

So, I believe my options are:

Call stored procedures from Access. I'm not sure how to do this yet.
Update the data in the sql server database with a date that wont be discarded on TSQL generation.
Try to find a way to make the generated TSQL include the explicit 12/30/1899 date. IDEAL

Any other ideas?

The curly braces are an ODBC escape sequence for the time. See: Date, Time, and Timestamp Escape Sequences — Joe Stefanelli
– Joe Stefanelli, Commented Nov 17, 2010 at 21:03
Is it possible to issue TSQL queries directly, or do they always go though this access translation step? — Donnie
– Donnie, Commented Nov 17, 2010 at 21:25
@Donnie - I don't know. I barely ever use Access. I'm trying to solve this problem for a friend. — Ronnie Overby
– Ronnie Overby, Commented Nov 17, 2010 at 21:28
Just by way of background... any COM-based app that goes handles dates uses 30 Dec 1899 as the date when handling times, not just access. If you cast the date/time 30 Dec 1899 00:00:00 to a number, you'll get zero, whereas 1 as a date/time is 31 Dec 1899 00:00:00, (and so on). Microsoft's Eric Lippert wrote a blog article here ( blogs.msdn.com/b/ericlippert/archive/2003/09/16/… ) on what 30 Dec 1899 actually is, and, more importantly why that date was selected as day zero. By comparison, SQL Server's datetime zero is 1 Jan 1900 00:00:00. — Callie J
– Callie J, Commented Nov 17, 2010 at 21:28
@Chris - I get it. But I need Access to not remove the date when generating TSQL. — Ronnie Overby
– Ronnie Overby, Commented Nov 17, 2010 at 21:33

Conrad Frix · Accepted Answer · 2010-11-19 16:26:53Z

1

You can change your where clause to the following which removes the date on the left side of the comparison

WHERE TimeValue(tblMainData.timeInMain) Between #12/30/1899 9:0:0# And #12/30/1899 17:0:0#

If you know that all values in the database will be on 12/30/1899 than you can just do

WHERE tblMainData.timeInMain Between #12/30/1899 9:0:0# And #12/30/1899 17:0:0#

As an aside if you want to do this in SQL Server Procedure you can strip the date using the following (its not much slower than if you knew the dates)

    WHERE 
         DATEADD( DAY , -1 *DATEDIFF(DAY, 0, timeInMain ) , timeInMain )
                    BETWEEN '09:00' and '17:00'

UPDATE

Here's how I tested it

I created a table and populated it using the following in SQL Server

CREATE Test 
( 
    id int identity,
    DateTimeValue DateTime
)

--Values with COM Zero Date
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1899 7:00 PM')

--Values with SQL Zero Date
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/1900 7:00 PM')

--Abritrary Date After Zero Dates
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 8:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 9:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 10:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 11:00 AM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 12:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 1:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 2:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 3:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 4:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 5:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 6:00 PM')
INSERT INTO Test (DateTimeValue) VALUES ('1/1/2010 7:00 PM')

I created a ODBC link to this table in MS Access 2007

And then created the following Query which results in the 27 records that have a time value between 9:00 AM and 5:00 PM

SELECT id, DateTimeValue
FROM dbo_Test
WHERE TimeValue(DateTimeValue) Between #12/30/1899 9:0:0# And #12/30/1899 17:0:0#

edited Nov 19, 2010 at 16:26

answered Nov 17, 2010 at 21:15

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Donnie Over a year ago

This also has the potentially horrific side-effect of also disabling indexes on the timeInMain column for this query.

Ronnie Overby Over a year ago

Using # instead of " around the time literals doesn't help.

Conrad Frix Over a year ago

@Donnie I realize its not Sargable but if you have a way that is (aside from adding a time column) you should provide it as answer

Ronnie Overby Over a year ago

@Conrad - That still doesn't work for me. Same error. Copying your code exactly results in a parameter input window opening.

Conrad Frix Over a year ago

@Ronnie I'm sorry I forgot to change the field names from my test to yours

|

Collectives™ on Stack Overflow

Access query for tables in linked SQL Server DB not returning data for search of datetime values as times

UPDATE

UPDATE 2

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE

UPDATE 2

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related