Thread: migration problem in SQL Query with date filter
Hello:
I´m new to postgresql and moving from access. Right ow I have a big project that rely heavely on date/time filters. in access the queries would look something like:
SELECT * FROM people WHERE timein > #3/1/2002 12:58#
This statement selects all the records where "timein" is greater than March 1 of 2002 and 12 hours 58 minutes AM.
The problem I have is how do I translate this kind of statement into SQL that postgresql understand while I retain compatibility with access.
I´m willing to change the code, but I need something that will work in Postgresql and access
I alsop read on the mailing list that I should use ISO dates (yyyy/mm/dd hh:mm:ss) This is no problem.
Thanks too anyone who can help
Hernán Breinbauer
Hernan, See http://techdocs.postgresql.org . There are several guides there for MS Access --> PostgreSQL migration. > SELECT * FROM people WHERE timein > #3/1/2002 12:58# > > This statement selects all the records where "timein" is greater than March 1 of 2002 and 12 hours 58 minutes AM. > > The problem I have is how do I translate this kind of statement into SQL that postgresql understand while I retain compatibility with access. > > I´m willing to change the code, but I need something that will work in Postgresql and access Can't be done. MS Access uses a non-standard delimiter for dates, namely the # sign. Postgresql cannot accept the # sign in place of the SQL standard single-quote '. You will have to have slightly different code for each database, or use an access library (such as ODBC) which does the transformation for you. Postges will accept readily either: SELECT * FROM people WHERE timein > '2002-03-01 12:58'; or SELECT * FROM people WHERE timein > to_timestamp('3/1/2002 12:58','M/D/YYYY HH:MI'); -- -Josh Berkus Aglio Database Solutions San Francisco
Access does not handle date time the way the rest of the world does. All dates must be encapsulated in pound signs. With postgresql, dates are surrounded by single quotes. This is a syntax problem with Access. :(
Thanks
Chad
----- Original Message -----Sent: Monday, October 07, 2002 2:50 PMSubject: [NOVICE] migration problem in SQL Query with date filterHello:I´m new to postgresql and moving from access. Right ow I have a big project that rely heavely on date/time filters. in access the queries would look something like:SELECT * FROM people WHERE timein > #3/1/2002 12:58#This statement selects all the records where "timein" is greater than March 1 of 2002 and 12 hours 58 minutes AM.The problem I have is how do I translate this kind of statement into SQL that postgresql understand while I retain compatibility with access.I´m willing to change the code, but I need something that will work in Postgresql and accessI alsop read on the mailing list that I should use ISO dates (yyyy/mm/dd hh:mm:ss) This is no problem.Thanks too anyone who can helpHernán Breinbauer