| Selecting from TIMESTAMP field
Oracle database |
StoutFiles
msg:4407710 | 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
msg:4407953 | 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
msg:4408122 | 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
msg:4408930 | 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
msg:4408968 | 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.
|
|
|
