Oracle Timestamp Conversion with Dates

Question

Assuming this has a simple solution, but I can't find it.

I'm trying to do some logic on a DATE field in Oracle. My desire is to take a DATE field and subtract X hours from it.

For instance: SELECT A.MyDATE - 100 Hours from dual;

however, I need a result in a timestamp format 'YYYY-MM-DD hh:mm'.

I've tried CAST(A.MyDATE as TIMESTAMP) - NUMTODSINTERVAL(100/24,'day') however it didn't work.

I found out that the issue is that the MyDATE field when cast to a timestamp still contained some residual time elements. How can I reset these??

Thanks!

First, define "didn't work". What was the result you got? What was the result you expected? When you talk about "residual time elements", are you trying to subtract 100 hours from midnight on whatever day MyDate is on rather than 100 hours from whatever time MyDate has? What data type do you want your query to return? The query you posted returns a timestamp but if you're talking about wanting a particular format that implies that you want to return a varchar2. — Justin Cave
– Justin Cave, Commented Feb 8, 2013 at 19:12
Sorry for not being clear enough. If MyDate was 2013-01-15 and I was hoping for to get 2013-01-10 20:00 as a result. I'm not getting this - I'm getting something like 2013-01-10 21:49. I found out that 'MyDate' when converted to a timestamp hold hours and minutes instead of 00:00 in it - not sure why. SO... I'm trying to zero out the time portion of MyDate after timestamp conversion, but I don't know how to do it. — NEW2WEB
– NEW2WEB, Commented Feb 8, 2013 at 19:17

Gordon Linoff · Accepted Answer · 2013-02-08 19:58:30Z

2

You can just do this with subtraction:

select a.MyDate - 100.0/24

To convert to varchar:

select to_char(a.MyDate - 100.0/24, 'YYYY-MM-DD')

And, if you want to get rid of that pesky time on the date:

select trunc(a.MyDate - 100.0/24) as JustTheDate

edited Feb 8, 2013 at 19:58

answered Feb 8, 2013 at 19:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Bracuk Over a year ago

You may want to add the to_char() that formats the result to your answer. Up to you.

NEW2WEB Over a year ago

Great answer, thanks! One question, how do I convert the to_char output back to a timestamp in order to compare to current system date?

Justin Cave Over a year ago

If I read the poster's comments correctly, they'd want to trunc the date before subtracting not after. Otherwise, in the example they posted, they'd get 2013-01-10 00:00:00 rather than 2013-01-10 20:00:00 if MyDate was 2013-01-15 01:49:00.

Art · Accepted Answer · 2013-02-08 19:37:07Z

2

The formats and dates in my example can be changed to any other formats and dates:

SELECT To_Timestamp(To_Char(Sysdate - INTERVAL '100' HOUR, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI')
 FROM dual
/

Output:

2/4/2013 10:18:00.000000000 AM


To remove time element add Trunc() to any of your dates...:

SELECT Trunc(To_Timestamp(To_Char(Sysdate - INTERVAL '100' HOUR, 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI'))
FROM dual
/

Output: 2/4/2013


Conversion/Casting - when using other dates in place of sysdate then add formats as in my other examples:

SELECT CAST(SYSDATE AS TIMESTAMP) - INTERVAL '100' HOUR FROM dual
/

Output: 2/4/2013 10:26:35.000000000 AM


SELECT start_date tstamp_to_date, CAST(start_date AS timestamp) date_to_tstamp FROM
 (
  SELECT to_date(to_char(to_timestamp ('2013-02-07 10:07:47.000' , 'YYYY-MM-DD HH24:MI:SS.FF'),'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') start_date
  FROM dual
 )
/

Output:

tstamp_to_date          date_to_tstamp 
-------------------------------------------------------
2/7/2013 10:07:47 AM    2/7/2013 10:07:47.000000 AM

edited Feb 8, 2013 at 19:37

answered Feb 8, 2013 at 19:15

Art

5,8222 gold badges24 silver badges22 bronze badges

1 Comment

Art Over a year ago

I guess true Oracle examples are too complicated for the user )

Justin Cave · Accepted Answer · 2013-02-08 19:26:22Z

In Oracle, a DATE always has a day and a time component. Depending on the tool you are using and your session's NLS_DATE_FORMAT, it is entirely possible that the tool may not display the time component when you look at the data. But that is simply a display question, it has no impact on the actual data.

If you want to subtract 100 hours from midnight on the day that MyDate represents

SELECT TRUNC(MyDate) - interval '100' hour
  FROM dual

This will return a DATE. If you want to return a string in a particular format

SELECT TO_CHAR( TRUNC(MyDate) - interval '100' hour,  'YYYY-MM-DD hh:mi am' )
  FROM dual

Note that I'm assuming that there was a typo in your question. I assume that you want to display the minutes after the hour (mi) rather than the month (mm).

Raj · Accepted Answer · 2013-03-08 19:52:01Z

0

I am trying to fetch the records which is older than 30 days (from Mod_date) and I am using the below query and it is returning all the data and I want only 30 days old data.

Sample :- Mod_date 03-NOV-12 12.00.00.000000000 AM

Query :-

select Mod_date from fil_cnfact where Mod_date <= sysdate -30 order by Mod_date asc ;

answered Mar 8, 2013 at 19:52

Raj

11 gold badge1 silver badge2 bronze badges

Collectives™ on Stack Overflow

Oracle Timestamp Conversion with Dates

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related