homepage Welcome to WebmasterWorld Guest from 54.166.84.82
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Selecting from TIMESTAMP field
Oracle database
StoutFiles

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4407708 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4407708 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4407708 posted 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

WebmasterWorld Senior Member penders us a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



 
Msg#: 4407708 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4407708 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved