0

In my Oracle DB, I have a date field called HIGH_DATE. The format for some entries is "27-SEP-12" (DD-MON-YY) and for some entries it is "27-09-12" (DD-MM-YY).

Can someone help me in framing a select query through which I can get dates in either formats??

MT0
  • 143,790
  • 11
  • 59
  • 117
Krishna2710
  • 11
  • 2

1 Answers1

0

If you have a DATE column then it does not have any format; it is stored internally as 7-bytes (century, year-of-century, month, day, hour, minute, second) and it is only when the user interface being used to access the database returns data to the user that it then gets formatted (and all the dates will be implicitly converted to strings with a consistent format).

I'm going to assume that when you say:

I have a date field called "HIGH_DATE"

What you actually mean is: "I have a column with a VARCHAR2 data-type where I store date values".

If that is the case then all you need to do is:

SELECT TO_DATE( high_date, 'DD-MM-RR' ) AS high_date
FROM   table_name;

Oracle's string-to-date conversion rules will match additionally the MON format model if you use the MM format model and don't specify an exact match using the FX format model.

If you have the test data:

CREATE TABLE table_name ( high_date ) AS
SELECT '23-09-20' FROM DUAL UNION ALL
SELECT '15-AUG-99' FROM DUAL;

Then the above query will output (depending on your NLS_DATE_FORMAT):

| HIGH_DATE           |
| :------------------ |
| 2020-09-23T00:00:00 |
| 1999-08-15T00:00:00 |

db<>fiddle here

However, the best solution is going to be to stop storing the values as strings and to store them (without a format) as a date.

MT0
  • 143,790
  • 11
  • 59
  • 117
  • The column "HIGH_DATE" is of data type TIMESTAMP(6). So the data type is good. Lets say I have 2M rows with the format DD-MON-YY and 2M rows with the format DD-MM-YY. By using a select query how can i see either DD-MM-YY format or DD-MON-YY. That is my question. – Krishna2710 Sep 24 '20 at 13:12
  • Because if you simply give select query , it will show all rows, how can i differentiate between these two formats using select query and how can i update from DD-MON-YY to DD-MM-YY. – Krishna2710 Sep 24 '20 at 13:14
  • @Krishna2710 That is impossible. Similar to `DATE`, a `TIMESTAMP` data type stores binary values and they do not have a format. If the user interface then displays a formatted version of the binary data then it will apply a consistent format to that data and will not apply multiple different formats. – MT0 Sep 24 '20 at 13:15
  • viewing(select query) is impossible or updating is impossible?? – Krishna2710 Sep 24 '20 at 13:18
  • @Krishna2710 It is impossible to have a `TIMESTAMP(6)` value with a specific format (let alone with multiple formats) because it is just binary data. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=2b0850c505e547d78261d98e77b119fc). What I am saying is that you don't have 2M rows with the format DD-MON-YY and 2M rows with the format DD-MM-YY; you have 4M rows with no format. – MT0 Sep 24 '20 at 13:21
  • Thanks @MT0 for your valuable explanation – Krishna2710 Sep 24 '20 at 15:08