asked on

Oracle - Add days and date format

Experts,
Two easy things, but combined I cant get them to work.

I want to add 3 days to a date in a column. The original date is in YYYYMMDD format and I need to keep the output format the same.

this works, but the output is in my default format, not YYYYMMDD

select (TO_DATE(:MyDate,'YYYYMMDD')-3)
from dual

Open in new window

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

The "trick" is you don't have a DATE value, you have text that you interpret as a date.

Hence the need to convert the text to a date, apply the math, and then convert that result back to text as shown above.

JDCam

ASKER

Thanks.
Note you are missing an open bracket. I added like this and it seems to work

select To_Char((TO_DATE(:MyDate,'YYYYMMDD')-3), 'YYYYMMDD')
from dual

Open in new window

awking00

The lesson to be learned here is to store dates as date data types and not characters. You can then apply all kinds of date math in addition to adding days and you can format them to display in almost any format. If your date was a date data type, it would just be select to_char(yourdate - 3,'YYYYMMDD')

JDCam

ASKER

Grrr.... The solution was already rejected by the department. The meant to say subtract working days, not calendar days. No concern with holidays, just exclude Sat and Sun.

I searched around and it looks like most answers rely on custom functions. Any way to do this in a SQL query

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

You indicated that you wanted to add three days to a date, but your example actually shows substracting three days. Perhaps you can explain what you're doing with the resulting date.

awking00

For example, are you trying to get records from the three prior three business days or just the one record from three business days ago. And, as slightwv asked, will you want this if the beginning date is a Saturday or Sunday?

JDCam

ASKER

I confirm Sunday = 1
Assume my starting date is always a weekday. I need to subtract 3 work days. resulting date should always be a weekday

Start date Aug 24 (Mon) should return Aug 19 (Wed)

awking00

Then slightwv's try query will work.

JDCam

ASKER

Yes.. works perfect.
Thanks for your help, and sorry for shifting the original question