DayName CurrentDate in SQL

[jhogstro]

I have a variable filtering out yesterday 06:30 to 06:30 in a report, and if it's Monday I go -3 to get from Friday to Monday, like so:

[v_Filter yesterday 0630-0630]

= If (
ToNumber(FormatDate([Timestamp tracking];"yyyyMMddHHmmss"))
Between (
ToNumber(Concatenation(FormatDate(RelativeDate(CurrentDate(); If (DayName(CurrentDate()) = "Monday" ;-3;-1)  );"yyyyMMdd");"063000")); ToNumber(Concatenation(FormatDate(CurrentDate();"yyyyMMdd");"062959"))
)
) Then "Y" Else "N"

But I would like to do it in the SQL instead where I have this from this object to expand on:

   ( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-1), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 062959')  )

I don't know SQL but I tried variants of this but didn't get it to work. "Error: missing right parenthesis". Is it possible?

...
AND
CASE WHEN Right(to_char((sysdate-1), 'YYIWD'),1) = '7' THEN
   ( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-3), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
ELSE
   ( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-1), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
END
AND
...

[nscheaffer · Accepted solution]

It looks to me from your SQL syntax that your database is Oracle. I don't have a lot of Oracle experience, but I'll attempt an answer.

The following links suggest you are using the CASE expression incorrectly...

https://stackoverflow.com/questions/18104884/conditional-where-clause-with-case-statement-in-oracle

https://dba.stackexchange.com/questions/1170/oracle-sql-case-in-a-where-clause

https://asktom.oracle.com/pls/apex/asktom.search?tag=case-construct-with-where-clause

Given those examples I think this variation using a CASE expression should work...

...
AND
CASE WHEN Right(to_char((sysdate-1), 'YYIWD'),1) = '7' 
      AND ( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-3), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
	 THEN 1
	 WHEN Right(to_char((sysdate-1), 'YYIWD'),1) <> '7' 
      AND ( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-1), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
	 THEN 1
	ELSE 0
END = 1
AND
...

Or you could eliminate the CASE expression completely with this approach...

...
AND (
		(
			Right(to_char((sysdate-1), 'YYIWD'),1) = '7' AND
			( tochar(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-3), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
		)
	 OR 
		(
			Right(to_char((sysdate-1), 'YYIWD'),1) <> '7' AND
			( to_char(GOB_SYS.TBI050.DUPDATE, 'YYIWD HH24MISS') between CONCAT(to_char((sysdate-1), 'YYIWD'),' 063000')  and CONCAT(to_char((sysdate), 'YYIWD'),' 063000')  )
		)
	)
AND
...

Hope that gives you some ideas.

Noel

[jhogstro · Accepted solution]

Cool, thanks! I tried and got it to work with your second example after reading up on Oracle. Turns out Oracle doesn't have Left() and Right() functions so I just changed it to Substr().

//Jan