Working with dates in SQL query using MDB-Tools

Question

I have been working on a project where I have to fetch data from MS-Access database. I am using mdb-sql tool for running sql queries into that database. But the problem occurs when I try to put where clause on a datetime column. It always throws a syntax error.

Please help me how can I use dates in where column. I have tried using these queries -

SELECT * FROM table1 WHERE checkintime > '04/22/13 12:15:39'

SELECT * FROM table1 WHERE checkintime > #04/22/13 12:15:39#

Have you tried to specify the date in format like this: '20130422 12:15:39' ? — Andrey Gordeev
– Andrey Gordeev, Commented May 22, 2013 at 7:13
No. I didnt try. Actually date format in the column is same as I mentioned in question , so i tried with same format — user1685501
– user1685501, Commented May 22, 2013 at 13:03

wineguy · Accepted Answer · 2017-07-25 00:04:10Z

3

mdb-sql displays human-readable dates in results, but requires timestamp values in queries -- calculated as the number of seconds sine 1/1/1970 (the beginning of the UNIX epoch).

So the correct query to extract all records with a date greater than 04/22/13 12:15:39 would be:

SELECT * FROM table1 WHERE checkintime > 1366650939

An easy way to calculate this from the command line is:

 date --date='04/22/13 12:15:39' +"%s"

answered Jul 25, 2017 at 0:04

wineguy

1766 bronze badges

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

Comments

Andre · Accepted Answer · 2017-02-17 11:51:24Z

1

You can use MS Access: Format Function (with Dates):

where Format([checkintime ],"yyyy-mm-dd hh:nn:ss")>='2013-04-22 12:15:39'

answered Feb 17, 2017 at 11:51

Andre

7323 gold badges8 silver badges28 bronze badges

Collectives™ on Stack Overflow

Working with dates in SQL query using MDB-Tools

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related