date between (1 Viewer)

adnanhome

Registered User
Joined
Nov 12, 2008
Messages
16
Hi I am trying to get :
if today is Monday then it will show result from Friday to Sunday
Else
one day before . means if today is Tue then I will show only Monday data.

--------------------------------------------------------------------
select * from POTABLE where PODATE BETWEEN

CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-3 AND CONVERT(CHAR(8), GETDATE(), 112)
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1 AND CONVERT(CHAR(8), GETDATE(), 112)
END

error
Incorrect syntax near the keyword 'AND'

please note my PODATE is 'yyyymmdd' format. where I am making mistake your help is appreciated. Thanks
 
Last edited:

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
Hi. Maybe you meant to use the + sign instead of AND?

Edit: That's probably not it. How about using only one AND after the END?
 

adnanhome

Registered User
Joined
Nov 12, 2008
Messages
16
I figure out myself. is this ok ?

select * from POTABLE where PODATE BETWEEN

CASE
WHEN
DATENAME(WEEKDAY, GETDATE())='Monday'
THEN
CONVERT(CHAR(8), GETDATE(), 112)-3
ELSE
CONVERT(CHAR(8), GETDATE(), 112)-1
END

AND CONVERT(CHAR(8), GETDATE(), 112)
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,619
Hi. Glad to hear you got it sorted out. As long as it works. Cheers!
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom