1

For example with the following query:

SELECT first_name, last_name, TO_CHAR(birthday, 'MM/DD/YYYY') as birthday FROM master

Is there some way I can forgo the TO_CHAR declaration and have the database auto convert the values for me? I've been trying to find a dynamic solution such that it autoconverts any value that's in a DATE column in every table, but to no avail.

Ideally I would like to just run the following query:

SELECT first_name, last_name, birthday FROM master

To get all values in their desired format. Is it also possible to do the vice versa from:

INSERT INTO master (first_name, last_name, birthday) 
VALUES (:first_name, :last_name, TO_DATE(:birthday, 'MM/DD/YYYY'))

to:

INSERT INTO master (first_name, last_name, birthday) 
VALUES (:first_name, :last_name, :birthday)
MT0
  • 143,790
  • 11
  • 59
  • 117
bartholomew
  • 102
  • 8
  • This answer might help you: https://stackoverflow.com/a/50164234/14853083 – Tangentially Perpendicular Mar 12 '21 at 23:47
  • 1
    `MM/DD/YYYY` is not a good format to store as searching and comparisons are complicated. Use `YYYY-MM-DD` or `YYYYMMDD`. – AbraCadaver Mar 12 '21 at 23:51
  • 1
    @AbraCadaver - given the OP, it's obvious the column is a DATE, not a CHAR or VARCHAR. So it is _not_ being stored in 'MM/DD/YYYY' or any other character format. He's wanting to know how to convert _from_ that string format _to_ the DATE type, without having to use TO_CHAR or TO_DATE. – EdStevens Mar 13 '21 at 00:15

2 Answers2

6

If you start with a date but Oracle needs it as a string (for example for displaying the output!) you either use TO_CHAR, with or without a format model (and a few other parameters as needed), or you don't use anything and Oracle calls TO_CHAR for you implicitly, with default values for the parameters. The most important, of course, is the date format model. This is a session parameter called nls_date_format; to see all your nls settings, you can run

select * from v$nls_parameters;

Conversely, if you start with a string but Oracle needs a date (to insert in a column of date data type, for example, or to use in some date computation, etc.), either you use TO_DATE, with or without a date format model, or Oracle calls it for you - again with the defaults, including nls_date_format.

Note that some (but not all) front-ends ignore your nls_date_format and use a setting you find in the user interface. Dumb feature if you ask me, but you just need to know about it.

Also note that timestamp and date are different, and they use different nls parameters for default formatting. And the same with timestamp with time zone. So if something doesn't seem right, consider the data types carefully.

On my system:

select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';


VALUE                                                           
----------------------------------------------------------------
dd-MON-yyyy

select sysdate from dual;   -- I didn't use `to_char` so Oracle did

SYSDATE    
-----------
12-MAR-2021

create table t (dt date);

insert into t (dt) values ('12-OCT-2020');  -- BAD PRACTICE!

select * from t;

DT         
-----------
12-OCT-2020

Now, be aware that the implicit conversion posse hunts down developers who abuse implicit conversions. Now that you know how it's done, don't ever do it anywhere that matters (for example in production). It's OK for quickly building up a test case while you are developing a query; exceptionally poor practice in general. Use TO_CHAR and TO_DATE as needed and you'll be fine!

  • you beat me to it. A very good explanation that pretty well explained it all. – EdStevens Mar 13 '21 at 00:29
  • 2
    See also: [Oracle's default DATE format](https://stackoverflow.com/a/50164234/1509264). The default varies depending on which territory is set on your database; so you should **NEVER** rely on implicit conversions when you are working in an international setting as users in different countries are likely to have different default `NLS_DATE_FORMAT` values. – MT0 Mar 13 '21 at 01:03
-3

To overcome all the headach of conversion of datatypes in your database try to change your date data type to timestamp and when you fetch it in your app try to convert it to date using date() function.

Dharman
  • 30,962
  • 25
  • 85
  • 135
MOUAD NASSRI
  • 53
  • 1
  • 1
  • 8
  • The OP's question is about getting the database to do the work. Suggesting they do the work in their application is wide of the mark. – Tangentially Perpendicular Mar 13 '21 at 00:07
  • I downvote because it is bad advice. Telling the OP he should change his DATE column to a timestamp "to overcome all the headach of conversion of datatypes ", then have to convert to DATE in the app? With the additional conversion to/from strings? Seriously? – EdStevens Mar 13 '21 at 00:27