get the previous date in oracle sql

user12251389

I have below query which gives current date. I want to return the value as String for this reason i used TO_CHAR.

select NVL(TO_CHAR(sysdate,'DD.MM.YYYY'),0) from dual

But i need to modify the logic such that it should always give the date from the yesterday.

This logic is very simple when the query runs from every Tuesday till Friday. I just need to use below query for this:

select NVL(TO_CHAR(sysdate - 1,'DD.MM.YYYY'),0) from dual

But there is exception. When the query runs on every Monday it should give the date from last Friday. And i dont know how i can achieve all this logic in the same query.

For example when the query runs today it should return the date from last Friday i.e 18.02.2022. When the query runs tommorow it should return the date from Today 21.02.2022.

I want to avoid dates from every Saturday and Sunday.

Gianni Ceresa

Not sure what the link with ODI is, but that's a detail (a MOD will move the thread where it belongs if needed).

Your query is quite strange to start with: have you experienced many cases when SYSDATE isn't set and the TO_CHAR of it returns NULL?

And your TO_CHAR returns a string, but your NVL returns a number : data types exists for very good reasons! It should be a number or a string, but it can't be both at the same time (and letting the database do implicit casting hoping for the best).

 it should always give the date from the previous date

And what is the meaning of previous date in your case? Do you mean of the previous day (yesterday) ?

select to_char(sysdate -1, 'DD.MM.YYYY') from dual

user12251389

@Gianni Ceresa sorry for the confusion ...i edited my question for correction...Did it create the thread in ODI ?

actually i am using this query in ODI where i wanted to return the query as String thats why i used TO_CHAR.

In my query i dont want to get the date from Saturday and Sunday as this is for example not working day.

Thats why i mentioned the query should return previous date from Tuesday till Friday and on Monday when the query runs then it should return the date from last Friday.

For example when the query runs today then it should return the date from 18.02.2022. When the query run tommorow it should return the date from today 21.02.2022.

Gianni Ceresa

In my query i dont want to get the date from Saturday and Sunday as this is for example not working day.

And what about Christmas? Or New Year? Or any other official public holiday?

If your dates have a business meaning of "working days", you can't expect to get them like that based on SQL formulas. You should use a date dimensions having all the required attributes identifying dates that are valid working dates for your own organization.

If you only wants to skip Saturday and Sunday (but again, if you start with that, next you will ask for skipping some other dates, and here you have to go back to the beginning of this message and read that), you should use conditions based on the day of the week and doing a -2 or -3 depending on the day of week. Oracle SQL has all you need for that, add it into a CASE WHEN and job done.