CODEWITHSUNDEEP
datetimeoracle-sqldeveloper
ASuit
ASuit

Reputation: 145

subtracting time for date time in SQL Developer

I am using SQL Developer (3.1.06) to pull information from DWHP. I want to subtract time (hh:mm:ss)--> timecol_3 from date time (dd/mm/yy hh:mm:ss)-->datetime_col4, so that something like (10/01/15 01:00:00)-(03:00:00) should display (09/01/15 22:00:00).

Now the date time wasnt combined intially and neither were they in the above format. (they were in mm/dd/yyyy and hhmmA or hhmmP format), So I used the following syntax to bring it to the (dd/mm/yy hh:mm:ss) format-->

to_char(to_date(datecol_1 || ' ' || timecol2, 'dd/mm/yy hh24:mi:ss'), 'dd/mm/yy hh24:mi:ss') as datetime_col4

Could you please help??

Upvotes: 0

Views: 725

Answers (1)

Sentinel
Sentinel

Reputation: 6459

It looks like your data is in string format (based on the manipulations performed in your bolded text):

to_char(to_date(datecol_1 || ' ' || timecol2, 'dd/mm/yy hh24:mi:ss'), 'dd/mm/yy hh24:mi:ss') as datetime_col4

In oracle it's best to work with dates in the native date type rather than constantly converting them to and from character data types, so datetime_col4 would be better represented like so:

to_date(datecol_1 || ' ' || timecol2, 'dd/mm/yy hh24:mi:ss') as datetime_col4

Now assuming timecol_3 is a character string also in the hh24:mi:ss form where the hh24 portion ranges between 00 and 23, you can cast timecol_3 to an interval data type (the closest data type oracle has to a pure time data type) like so:

cast('0 '||timecol_3 as interval day to second) timecol3

You need the leading '0 ' when casting to an interval to represent the day portion of the interval, in this case zero days.

Interval data types can be added and subtracted to and from each other and to and from date[time] data types natively in oracle, so to get your final result as a date date type (starting from your base columns):

  to_date(datecol_1 || ' ' || timecol2, 'dd/mm/yy hh24:mi:ss')
- cast('0 '||timecol_3 as interval day to second) result

You can wrap that with a final to_char() to display the results in your desired format, or change your NLS_DATE_FORMAT session setting to output all date data types in your desired format:

ALTER SESSION NLS_DATE_FORMAT = 'DD/MM/YY HH24:MI:SS';

Upvotes: 0

Related Questions