PL/SQL (MOSC)

MOSC Banner

How to setup the default 4-digit year as '19xx' for xx>'60' and '20xx' for xx<=60 without using trig

edited Jan 30, 2020 2:08PM in PL/SQL (MOSC) 4 commentsAnswered ✓

Hi,

Our daily data loading script is using SQL Loader to text file with date format as 'YY/MM/DD' .

For example:

81/11/19

18/01/21

87/03/23

90/08/13

96/05/05

After data were loaded, all of them are saved as

2081/11/19

2018/01/21

2087/03/23

2090/08/13

2096/05/05

We want them as below:

1981/11/19

2018/01/21

1987/03/23

1990/08/13

1996/05/05

We don't want to use trigger on stage table when load data.

SQL> select to_char(to_date('98/09/26','YY/MM/DD'),'YYYY/MM/DD') FROM DUAL;

TO_CHAR(TO

----------

2098/09/26

Is there any default setting on database? Do I have to update the SQL Loader control file?

Thanks

Xiaogang

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center