cancel
Showing results for 
Search instead for 
Did you mean: 

DayName CurrentDate in SQL

jhogstro
Discoverer
0 Kudos
172

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
...

Accepted Solutions (1)

Accepted Solutions (1)

nscheaffer
Active Contributor
0 Kudos

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
Discoverer
0 Kudos

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

Answers (0)