目录
Previous day date excluding weekend
sqloracle
浏览量:55
编辑于:2023-04-06 12:44:21

I am trying to create an SQL query in TOAD that retrieves the data from an Oracle DB. What it should do is retrieve all data from previous workday back to one week, but it should exclude Saturday and Sunday. So far I am created the query below. In this format it returns me a "Missing keyword" error message. TOAD highlights my first "Between" operator. I have the idea that I am nearly there. Who can give me the final push into the right direction.

Thanks.

Select orderkey, actualshipdate
from orders
Case to_char(sysdate, 'DAY')
When 'MONDAY'
then to_char(actualshipdate,'YYYYMMDD') between to_char(sysdate, 'YYYYMMDD') -11 and  to_char(sysdate, 'YYYYMMDD')-3
else to_char(actualshipdate,'YYYYMMDD') between to_char(sysdate, 'YYYYMMDD') -8 and  to_char(sysdate, 'YYYYMMDD')-1
end
order by actualshipdate asc
解决方案:

Based on your description (and replacing the awful to_char of the dates):

select orderkey, actualshipdate
from orders
where 1=1
and actualshipdate between trunc(sysdate) - 8 
                     and trunc(sysdate)-(1/86400) -- midnight last night minus 1 second
and to_char(actualshipdate, 'D') not in (6,7) -- 6: Sat, 7: Sun
上一篇
Mqtt 客户端无法同时处理多条消息
下一篇
静态文件不提供，但可直接访问。Django， docker， nginx

加密保护您的代码与数据