Datepart

[stephm923]

The query I'm trying to build pulls data from the day before. For example, on Tuesday – Friday previous day and
on Monday I'm pulling Friday through Sunday. This is the date query I'm using, but I'm getting null values for effdate and enddate. Can you please take a look at it and let me know what I'm doing wrong. Thanks, Stephanie

DECLARE @TODAY DATETIME
DECLARE @EFFDATE DATETIME
DECLARE @ENDDATE DATETIME
DECLARE @wkday int

SET @Today = CAST(CONVERT(nvarchar(10), GETDATE(), 120) AS datetime) -- strips time off
set @wkday = DATEPART(dw, GETDATE())
--GET THE DATES FOR THE JOB
If @wkday <> '2' And @wkday <> '1' And @wkday <> '3'--Monday, Sunday, Tuesday
BEGIN
SET @ENDDATE = DATEADD(MS, -2, DATEADD(dw, 0, @Today))
SET @EFFDATE = DATEADD(dw, -1, @Today)
END
ELSE
If @wkday = '3'--'Tuesday'
BEGIN
SET @ENDDATE = DATEADD(MS, -2, DATEADD(dw, 0, @Today))
SET @EFFDATE = DATEADD(dw, -3, @Today)
END
--SELECT @TODAY,@ENDDATE,@EFFDATE,@wkday

 

[AlexCuse]

Your problem is this:

If @wkday <> '2' And @wkday <> '1' And @wkday <> '3'--Monday, Sunday, Tuesday

You will never have all of these evaluate to true.

Try changing the 'and' s to 'or' s.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[stephm923]

Duh, thanks a bunch

 

[AlexCuse]

No problem, glad you got it working

Ignorance of certain subjects is a great part of wisdom