date between (1 Viewer)

adnanhome

Registered User.
Local time
Today, 15:09
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
Local time
Today, 15:09
Joined
Oct 29, 2018
Messages
8,737
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.
Local time
Today, 15:09
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
Local time
Today, 15:09
Joined
Oct 29, 2018
Messages
8,737
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