Oracle 19c
I receive a file from one of the vendors with extract_date: 0912 (month/date).
I need to be able to construct valid Oracle date in mm/dd/yy format that is a day prior to the extract_date.
Example:
extract_date: 0912
desired_result: 09/11/24
I tried the following:
select to_date(TO_CHAR(0912-1,'0999')||to_char(sysdate,'YY'),'mmddyyyy')
from dual
But, the result is: 9/11/0024
Can someone suggest a solution, please?
The immediate problem is that you’re doing to_char(sysdate,'YY') which gives you a 2-digit year value of 24, then converting that back to a date with mmddyyyy which expects a 4-digit year. As mentioned in the documentation, Oracle will, by default, pad 24 with zeros to get 0024 – which is what you’re seeing. So the initial answer is to use YYYY for both conversions:
select to_date(TO_CHAR(0912-1,'0999')||to_char(sysdate,'YYYY'),'mmddyyyy')
from dual
2024-09-11
fiddle
But don’t do that. It works for some values, but won’t for others. If your starting values was 0901, for example, it would get an error – because subtracting 1 would try to convert 09002024, and that is not a valid date representation (fiddle).
Instead, convert the value as it is to a date, then subtract 1 afterwards:
select to_date(TO_CHAR(0901,'0999')||to_char(sysdate,'YYYY'),'mmddyyyy') - 1
from dual
2024-08-31
or even just
select to_date(TO_CHAR(0901,'0999'),'mmdd') - 1
from dual
2024-08-31
as it will default to the current year if you don’t supply one.
fiddle
You said you get the value 0912 from a file, which suggests it’s already a string – particularly as it’s zero-padded already; depending how you put that value into the query (hopefully with a bind variable?) you probably don’t even need the to_char() call, and can simplify further to:
select to_date('0912','mmdd') - 1
from dual
by supplying the value as a string.
You may also still have problems with certain dates, like at the end of the year, if you process the file later. For example, if you get a file at the end of this year with extract date 1231 but don’t process it until January 1st next year, the 1231 will convert to 2025-12-31, and subtracting 1 from that will give 2025-12-30 – not, as you’d presumably want, 2024-12-30.
Ideally your file would have the full date, but if that can’t be changed, you may need to build in some knowledge of when the file was actually created, or do some careful testing and manipulation for edge cases. How complicated that would need to be will depend on your process and what assumptions you can safely make, if any. (Such as just always subtracting 1 from sysdate befoe getting the YYYY value, if you know you always process the next day; but if you knew that you wouldn’t need to check the extract date at all…)