Need help in incrementing the date in oracle

dateoracleselect

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this.

select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' from DUAL;

Expected output (if :To_date variable is '09-DEC-2014'):

'10-DEC-2014'

Best Answer

Note the extra brackets.

SQL> variable d varchar2(100);
SQL> exec :d := '09-DEC-2014';

PL/SQL procedure successfully completed.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'D
--------------------
'10-DEC-14'

Changing date format:

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual;

''''||(TO_DATE(:D,'DD-
----------------------
'10-DEC-2014'

Related Solutions

MySQL – Using DATEDIFF() Function

Aaron's answer is essentially right, but I would not recommend DATEDIFF in the WHERE clause because it could possibly impose a table scan. Instead, I suggest this

SELECT
   user.*,
   company.*.
   DATEDIFF(Company.CreatedOn,NOW()) NumDays
from dbname.company
inner join dbname.user
on user.company_id = company.company_id
WHERE company.CreatedOn < DATE(NOW() - INTERVAL 1 YEAR);

You could take further and refactor the query as follows:

SELECT
   U.*,
   C.*.
   DATEDIFF(C.CreatedOn,NOW()) NumDays
from
(select * from dbname.company
WHERE CreatedOn < DATE(NOW() - INTERVAL 1 YEAR)) C
inner join dbname.user U
on U.company_id = C.company_id;

Oracle Date Formatting – Best Practices and Examples

figured this out, here is a solution in case someone needs to do something similar...

using double quotes "" around characters will display them as they are...

select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual

will display:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen

Best Answer

Related Solutions

MySQL – Using DATEDIFF() Function

Oracle Date Formatting – Best Practices and Examples

Related Question