CODEWITHSUNDEEP
sqloracle-database
c12
c12

Reputation: 9827

Retrieve Last days data from TIMESTAMP(6) Column

I have a oracle TIMESTAMP(6) column (11g) that I'm trying to use in various queries to get the last day, last hour...etc data. When I run the below it returns all the data. I would expect it to not return any data as the data_initiated column should not be greater than the current system time.

select * from table_name where date_initiated > SYSTIMESTAMP order by date_initiated desc

returns:

2014-04-09 18:35:55.414000
2014-04-09 18:20:30.020000
2014-04-09 18:19:29.052000
2014-04-09 18:19:03.461000
2014-04-09 18:12:25.231000
2014-04-09 17:40:57.262000
2014-04-09 17:10:47.058000
2014-04-09 16:12:57.509000
2014-04-09 16:09:28.769000
2014-04-09 16:08:02.386000
2014-04-09 15:20:48.896000
2014-04-09 14:32:10.713000

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

returns : 

04-09-2014 18:45:49

Upvotes: 0

Views: 269

Answers (1)

Roberto Navarro
Roberto Navarro

Reputation: 958

I would imagine this should work--minus however many days you want to look back:

select * from table_name where TRUNC(date_initiated) = TRUNC(SYSDATE-1)

Upvotes: 1

Related Questions