I'm trying to convert a char (output from a substr operation) into a dateTime format using TO_DATE with the following format - hh24:mi:ss.

The output of the substr looks fine but as soon as I run it through the TO_DATE function it converts every row for this column into 01-MAR-22.

To demonstrate I have the following:-

SUS_TIME2 shows what I get back from the SUBSTR and it looks fine at this point. SUS_TIME3 then shows what I get back after running it through the TO_DATE function, this where it converts it to 01-MAR-22

,SUBSTR((s.resolve_date-suspend_date),-16,9) sus_time2

,TO_DATE(SUBSTR((s.resolve_date-suspend_date),-16,9), 'hh24:mi:ss') sus_time3

Can anyone see what's going on here please? Thanks

CodePudding user response：

Dates are stored in an internal representation and do not have any intrinsic format. What you are seeing is how your client is choosing to format the actual date value as a string for display, using your session's NLS_DATE_FORMAT setting (not always the case, but this is SQL Developer, so it is here.)

When you call to_date() with only the time components it defaults the date part to the first day of the current month, which is why you are seeing March 1st. That is a bit buried in the documentation:

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

But it is setting the time properly on that date, which you can see by either explicitly converting the date back to a string:

to_char(<your date>, 'YYYY-MM-DD HH24:MI:SS')

or by changing your session:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'

db<>fiddle

But you should not rely on NLS settings for anything except ad hoc queries; someone else running your code may have different settings. And only convert back to a string at the last moment when you have to display the value in a particular format - store it and pass it around as a native date.

Oracle doesn't have a time-only data type, so if you really only care about the time part then you can use a date (either defaulting to current month, or using an explicit fixed date) and ignore the date part; or potentially use an interval; or use the number of seconds the time represents (i.e. 0-86399). Which is suitable depends on what you'll use the value for.

It looks like you might be substringing the result of subtracting two timestamps; in which case (a) you already have an interval, and (b) you probably need to allow for that difference to span more than one day. You can also extract the individual time components directly from an interval value. So I'd question whether your approach is really appropriate.

CodePudding user response：

You appear to be calculating s.resolve_date-suspend_date which gives you an INTERVAL DAY TO SECOND data type (implying that one or both of s.resolve_date or suspend_date is a TIMESTAMP data type) and then using SUBSTR to extract the time component of the data type and trying to convert that to a date and then display the time component.

Don't do that as using SUBSTR is fragile as it depends on the number of decimal places that the INTERVAL has which, in turn, will depend on the number of fractional seconds that the TIMESTAMP values have.

---

Just pick a date and add the interval to it and then format it as string to display it:

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(
         DATE '1900-01-01'   (s.resolve_date-suspend_date),
         'hh24:mi:ss'
       ) sus_time
FROM   table_name s

Which, for the sample data:

CREATE TABLE table_name (resolve_date, suspend_date) AS
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP(6)),
       CAST(TRUNC(SYSTIMESTAMP) AS TIMESTAMP(6))
FROM   DUAL;

Outputs:

RESOLVE_DATE	SUSPEND_DATE	SUS_TIME
2022-03-25 12:59:15.223445	2022-03-25 00:00:00.000000	12:59:15

---

From your comment:

all i'm trying to do is format the data so that when it's exported to Excel sorting works correctly.

That really is an XY-problem. You can solve it in Excel by either specifying the column format as a time when you import the data into Excel or right-click on the column header and "Format" the column picking the "time" data type with the correct format model.

You can also output the time as a fraction of a day with a numeric data type and then Excel can format it as the correct time using either:

SELECT s.resolve_date,
       suspend_date,
       MOD(CAST(s.resolve_date AS DATE)-CAST(suspend_date AS DATE), 1) AS sus_time
FROM   table_name s;

or

SELECT s.resolve_date,
       suspend_date,
       TO_CHAR(DATE '1900-01-01'   (s.resolve_date-suspend_date), 'SSSSS')
         / 86400 AS sus_time
FROM   table_name s

Which both output:

RESOLVE_DATE	SUSPEND_DATE	SUS_TIME
2022-03-25 13:16:40.204461	2022-03-25 00:00:00.000000	.5532407407407407407407407407407407407407

Which may not be human readable in that format but Excel will reformat it in a time column to 13:16:40.

db<>fiddle here