Oracle - how to create date from an already existing date?

Question

Say I issue:

select date_field from table1;

date_field is like '25.11.2009' I will try to change the positions of date fields with the month and vice versa. (of course for days > 12 some manipulations)

 TO_DATE( MOD(SUBSTR(TO_CHAR(a.A_DATE, 'DD.MM.YYYY'), 4, 2), 12) || '.' ||
              SUBSTR(TO_CHAR(a.A_DATE, 'DD.MM.YYYY'), 1, 2) || 
              SUBSTR(TO_CHAR(a.A_DATE, 'DD.MM.YYYY'), 6, 4),
          'DD.MM.YYYY')

THE THING IS THAT THE VALUE RETURNED FROM MOD() function is a number, i.e. for 01.07.2009 --> I get 1 for date, not '01' as expected. Later on I cannot get the date. Is there a shortcut solution to my problem?

Answer 1

Does this work?

TO_DATE(TO_CHAR(a.A_DATE, 'DD/MM/YYYY'), 'MM/DD/YYYY')

Answer 2

I used: CASE MOD(SUBSTR(TO_CHAR(a.birthday, 'DD.MM.YYYY'), 1, 2), 12) WHEN 1 THEN '01' WHEN 2 THEN '02' WHEN 3 THEN '03' WHEN 4 THEN '04' WHEN 5 THEN '05' WHEN 6 THEN '06' WHEN 7 THEN '07' WHEN 8 THEN '08' WHEN 9 THEN '09' WHEN 10 THEN '10' WHEN 11 THEN '11' WHEN 12 THEN '12' END not very elegant but works :)

Answer 3

Have you tried TO_CHAR(, '') ?

Answer 4

I suspect you need to seriously reconsider what you are trying to do.

I think what you started with is that you want to simply change the formatting of the date, e.g. change '25.11.2009' to '11.25.2009'.

If date_field is actually a DATE type, there is no inherent formatting stored in the field. It is a numeric value representing a specific date and time. It is formatted into text when you SELECT it in SQLPlus or some other tool, but that formatting is not stored in the table.

If you want to view the date in a particular format, you use the TO_CHAR function to force it. You can also set a default format model for a single session, a single client, or the whole database using NLS_DATE_FORMAT.

Answer 5

yli> "I am somehow anonymising the date, actually Hire Date of employees, to be used in a test environment."

So here was your actual requirement. If you want to change some dates, you don't need to use the date/string conversion functions (TO_CHAR/TO_DATE) at all. Oracle supports arithmetic operations on date values directly.

If you want to randomize your dates why not just use something like:

select date_field + dbms_random.value * 100 from table1;