SQLTeam.com | Weblogs | Forums

Reset System Date For Selection Base on Day of Week

#1

We have a new software system in which we are allow to write SQL programs to pull user information. We need a SQL that on Monday the system date(selection) will automatic back date 3 days to previous Friday/ Selection on Sunday would back up 2 days to the previous Friday. Not having a lot of success. Any help we would be thankful. Code below:

select *
CASE WHEN TO_CHAR(sysdate, 'fmday') = 'sunday' THEN sysdate - 2
WHEN TO_CHAR(sysdate, 'fmday') = 'monday' THEN sysdate - 3
ELSE sysdate - 1
from sg00400T
where alert_cd = 'AUTO'
and chg_dtm >= sysdate

If we can get this basic SQL working it should be easier in the future.

0 Likes

#2

is this MySQL?

0 Likes

#3

Using ORACLE SQL Developer 4.1.4

0 Likes

#4

This is Microsoft SQL server forum. But maybe someone will respond

0 Likes

#5

Your case statement missing "END" Try this :see if it works? 'S' is an Alias name to your table.

select S.* , (
CASE WHEN TO_CHAR(sysdate, 'fmday') = 'sunday' THEN sysdate - 2
WHEN TO_CHAR(sysdate, 'fmday') = 'monday' THEN sysdate - 3
ELSE sysdate - 1) End as column_name
from sg00400T S ;
where alert_cd = 'AUTO'
and chg_dtm >= sysdate

0 Likes

#6

Is this MYsql?

0 Likes

#7

Google search allicdata

0 Likes

#8

Why?

0 Likes