CODEWITHSUNDEEP
ms-accessdb2
Jeff Brady
Jeff Brady

Reputation: 1498

How do I query a DB2 timestamp value from MS Access?

I have a linked DB2 table in Access 2007. The field "ROW_INSERT_TS" is shown as a Date/Time value, and some values look like this (in Access)

7/7/2014 2:39:25 PM
7/7/2014 2:36:04 PM
7/3/2014 12:18:25 PM

I'm trying to find all values >= 7/3/2014, which should include all 3 of the above.

I've tried:

WHERE (((MY_TABLE.ROW_INSERT_TS) >= '7/3/2014 0:00:00 AM'))

which gets a Data Type Mismatch error

WHERE (((MY_TABLE.ROW_INSERT_TS) >= '7/3/2014'))

gets the same as above

WHERE (((MY_TABLE.ROW_INSERT_TS) >= '2014-07-03'))

gets the same as above

WHERE (((MY_TABLE.ROW_INSERT_TS) >= #7/3/2014 0:00:00 AM#))

gets a 'SQL0180N The syntax of the string representation of a datetime value is incorrect'

WHERE (((MY_TABLE.ROW_INSERT_TS) >= #7/3/2014#))

gets the same as above

I've also tried changing the date format to 2014-07-03 which didn't have any effect.

I read about a possible fix by editing the db2cli.ini file and changing the PATCH value to 8, which I edited to include PATCH1=8 but that didn't change anything.

What else can I try?

Upvotes: 1

Views: 2073

Answers (2)

bhamby
bhamby

Reputation: 15450

If you change the CLI Settings MAPTIMESTAMPDESCRIBE and MAPTIMESTAMPCDEFAULT to a value of 1, then the dates and timestamps will be mapped to their CHAR() equivalents, and Access (or the DB2 backend) will accept those values.

You will then use these formats:

Date - YYYY-MM-DD

Timestamp - YYYY-MM-DD-hh.mm.ss.ssssss

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91326

In MS Access, Date() is today without a time and Now() is this minute. For the most part, MS Access will accept:

 SELECT * FROM ThisTable WHERE ThisDateField > Date() + 15

That is, today + 15 days.

Upvotes: 1

Related Questions