views:

519

answers:

5

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?

A: 

Does this work?

TO_DATE(TO_CHAR(a.A_DATE, 'DD/MM/YYYY'), 'MM/DD/YYYY')
Adam Paynter
gives me "ORA-01843 not a valid month" error.
yli
Of course it does, because days range from 1 to 31, while months must be between 1 and 12. Which begs the question -- what are you even trying to accomplish?
Dave Costa
I am somehow anonymising the date, actually Hire Date of employees, to be used in a test environment.
yli
+1  A: 

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 :)

yli
LPAD(TO_CHAR(a.birthday, 'DD'), 2, '0')
Adam Paynter
Sorry, LPAD(MOD(TO_CHAR(a.birthday, 'DD', 2, '0'), 12))
Adam Paynter
Actually, yli, that won't work for all possible inputs because the output of the MOD expression will be between 0 and 11, not between 1 and 12.
Dave Costa
i changed: WHEN 0 THEN '12' thx.
yli
A: 

Have you tried TO_CHAR(, '') ?

Mike Curry
+4  A: 

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.

Dave Costa
A: 

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;
Jeffrey Kemp