0

I need to delete records inserted in the last minute and i have to use a specific system date that exists in my database and that is actually truncated (select sys_date from dual returns 20/3/2014). i tried to format the date to show minutes and then convert it again into date in order to select records inserted in the last minute using between sys_date si sys_date-1 minute, but it doesnt seem that i'm going in a good direction. for example:

select to_date(to_char((sys_date - 1 / (24 * 60)), 'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') from dual;

returns 19/3/2014 11:59:00 PM, which doesn't match my format mask and honestly i don't really understand (which is very frustrating) why as the string returned by the inner select

select to_char((sys_date - 1 / (24 * 60)), 'DD/MM/YYYY HH24:MI:SS') from dual;

returns 19/3/2014 23:59:00. any help or advice is highly appreciated:) thank you!

mardeea
  • 45
  • 1
  • 7
  • Is `sys_date` your own function, and how is it defined? What type does it return? Your first query suggests your NLS_DATE_FORMAT is 'DD/MM/YYYY HH:MI:SS AM', so if `sys_date` is only showing `20/3/2014` then it seems to be a string rather than an actual date. But then your inner query would throw ORA-01722, so something doesn't add up. – Alex Poole Jan 08 '15 at 16:52
  • yes sys_date is a standalone function. it returns a date (it applies the to_date funct. to the string returned by another function). as far as i can see it's truncated somewhere along the way and it doesnt seem that i can retrieve the time from it. guess i'll need to find a workaround of some sort. tnx anyway:) – mardeea Jan 09 '15 at 08:23
  • I'm not sure what your question is then; your query finds the minute before `sys_date` - just in a convoluted way, and Tony's answer gives a much cleaner way. If your `sys_date` isn't giving you the value you need I'm not sure we can help with that. Do you just want to know why the result is being shown in that format? – Alex Poole Jan 09 '15 at 08:28
  • yes i wanted to find out why the result is being shown in that format and also when i asked i thought i was doing something wrong along the way; yet after some more digging and testing on another db i realized i just can't get the time from my sys_date . that's why it was 11:59:00 PM, because it subtracted from 00:00:00 – mardeea Jan 09 '15 at 08:45

1 Answers1

0

To select rows inserted in the last minute:

select *
from   my_table
where  inserted_date > sysdate-1/(24*60);

(This assumes your table has a date column called inserted_date that holds the date and time of insertion.)

SYSDATE always returns the current date and time, but what you see in a tool like SQL Plus, SQL Developer or Toad depends on the default format mask used to convert it to a string to be diplayed. Typically by default these do not show the time. In SQL Plus you can do this:

SQL> alter session set nls_date_format='DD_MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
08_JAN-2015 16:37:00
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • unfortunately i can't use sysdate due to the purpose of the application (its date can differ from the date of the OS on which the db is installed on), but i'm gonna mark the answer as good as it solves the general issue. thank you – mardeea Jan 09 '15 at 08:25