CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Abc16
Abc16

Reputation: 11

Date Format in Oracle PlSql

This is existing code,can anyone explain it?

  var_l_year_days := to_number(to_char(to_date(to_char(to_date
 (ADD_MONTHS(var_l_mnth_details(i) .var_l_mnth_start_date, 12),
'DD-MM-YYYY'),'YYYY') ||'1231','YYYYMMDD'),'DDD'));

Upvotes: 1

Views: 54

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

to_date (ADD_MONTHS(var_l_mnth_details(i) .var_l_mnth_start_date, 12), 'DD-MM-YYYY')

This is a bug in your code. Never apply TO_DATE on a DATE. It forces Oracle to:

  • first convert it into a string
  • then convert it back to date

based on the locale-specific NLS settings. You need TO_DATE to convert a literal into date. For date-arithmetic, leave the date as it is.

Use:

  • TO_CHAR - to display the date in your desired format
  • TO_DATE - to convert a literal into date.

You could simplify the query in the following way:

SQL> SELECT add_months(TRUNC(SYSDATE,'year'), 24)
  2       - add_months(TRUNC(SYSDATE,'year'), 12) days_in_next_year
  3  FROM dual;

DAYS_IN_NEXT_YEAR
-----------------
              366

Upvotes: 1

Husqvik
Husqvik

Reputation: 5809

To me it seems returning number of days in year next to var_l_mnth_details(i).var_l_mnth_start_date. Basically finding out if next year is the leap year or not.

Alhough this can be simplified into:

var_l_year_days := TO_NUMBER(TO_CHAR(TRUNC(ADD_MONTHS(var_l_mnth_details(i).var_l_mnth_start_date, 24), 'YEAR') - 1, 'DDD'));

Upvotes: 0

Related Questions