CODEWITHSUNDEEP
sqloracle-databasedate
Sara Moradi
Sara Moradi

Reputation: 55

How do I convert a date to Shamsi/Persian format in Oracle SQL?

I want to convert date to Shamsi/Persian format, but it did not work well. My code:

SELECT TO_CHAR('10-JUN-18', 'dd-mm-yy','nls_calendar=persian') 
FROM dual;

I want it be like this: 20-03-97

Upvotes: 1

Views: 729

Answers (1)

MT0
MT0

Reputation: 168416

TO_CHAR( date_value, format_model, nls_parameters ) takes a DATE data type as the first argument.

'10-JUN-18' is not a DATE data type; it is a string literal (that just happens to look like a date). Oracle will try to perform an implicit conversion from a string to a date but this implicit conversion is failing (and it is bad practice to rely on implicit conversions).

If you use a DATE literal then your code works:

SELECT TO_CHAR(DATE '2018-06-10', 'dd-mm-yy','nls_calendar=persian') AS dt
FROM   DUAL;
DT
20-03-97

Or, you can explicitly convert the string literal to a date and then convert it back:

SELECT TO_CHAR(
         TO_DATE('10-JUN-18', 'DD-MON-RR', 'NLS_DATE_LANGUAGE=English'),
         'dd-mm-yy',
         'nls_calendar=persian'
       ) AS dt
FROM   DUAL;
DT
20-03-97

db<>fiddle here

Upvotes: 6

Related Questions