Selecting from TIMESTAMP field

Forum Moderators: open

Message Too Old, No Replies

Selecting from TIMESTAMP field

Oracle database

StoutFiles

2:44 pm on Jan 17, 2012 (gmt 0)

I cannot, for the life of me, select timestamp data between two dates.

I can get all the data:
$sql = "SELECT * FROM DATE_TABLE";

I can order the data and limit the row count:
$sql = "SELECT * FROM DATE_TABLE ORDER BY DATE_TIME DESC LIMIT TO 30 ROWS";

All of the following requests fail however:
$sql = "SELECT * FROM DATE_TABLE WHERE DATE_TIME BETWEEN '01-Jan-2012' AND '17-Jan-2012'";
$sql = "SELECT * FROM DATE_TABLE WHERE DATE_TIME BETWEEN '01/01/2012' AND '01/17/2012'";
$sql = "SELECT * FROM DATE_TABLE WHERE DATE_TIME BETWEEN '01-Jan-2012 08:23:27.00' AND '17-Jan-2012 08:32:27.00'";

Data echoed is in the format: 17-Jan-2012 08:23:27.00
Data as shown in database is in the following format: 2012011614292300

Error message received for all BETWEEN requests:
SQL error: [Oracle][ODBC][Rdb]%SQL-F-UNSDATXPR, Unsupported date expression -SQL-F-DATEEQLILL, Operands of date/time comparison are incompatible

Any help?

Dijkgraaf

11:20 pm on Jan 17, 2012 (gmt 0)

Try
$sql = "SELECT * FROM DATE_TABLE WHERE DATE_TIME BETWEEN to_date ('01/01/2012', 'mm/dd/yyyy') AND to_date ('01/17/2012', 'mm/dd/yyyy')";

StoutFiles

2:33 pm on Jan 18, 2012 (gmt 0)

Thank you for your help. My particular setup doesn't support to_date (Oracle RDB7) but it supports alternate equivalents so I looked into them till I found an answer. Here was what worked for me.

$sql = "SELECT * FROM DATE_TABLE WHERE CAST(DATE_TIME AS CHAR(24)) BETWEEN CAST('2012-01-16 07:23' AS CHAR(24)) AND CAST('2012-01-18 08:23' AS CHAR(24)) ORDER BY DATE_TIME DESC";

penders

10:26 am on Jan 20, 2012 (gmt 0)

Just a thought... won't this perform a string comparison, rather than a date comparison, and be less efficient?

StoutFiles

1:12 pm on Jan 20, 2012 (gmt 0)

Possibly. It's for a company intranet and will be rarely used, so I'm not shooting for max efficiency, just a workable solution.