using date between in sql where clause

Question

I have a table A with origdt as a column in it. I want to pull all the records from the table based on the following conditions

if today is tuesday then Origtdt between saturdaymorning to yesterday evening else Origtdt between yesterday morning to yesterday evening end

Here is the query i wrote and it is giving me an error.

SELECT *
FROM A
WHERE 
    CASE 
        WHEN DATENAME(dw, GETDATE()) = 'Tuesday' 
        THEN 
        ( 
            OrigDt BETWEEN 
                CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), GETDATE() - 3, 101)) 
                AND DATEADD(SS, -1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101)))
        ) 
        ELSE 
        (
            OrigDt BETWEEN 
                CONVERT(SMALLDATETIME, CONVERT(VARCHAR(10), GETDATE() - 1, 101)) 
                AND DATEADD(SS, -1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 101)))
        ) 
    END

Error is : incorrect syntax near Between

To check, if it's Monday would you also want the data from start-of-day Saturday, or only from start-of-day Sunday? — JohnLBevan
– JohnLBevan, Commented Feb 19, 2015 at 18:43
To make it language neutral you could use DATEPART(dw, GETDATE()) = 3 in place of DATENAME(dw, GETDATE()) = 'Tuesday'; that's also a very minor performance optimisation. Admittedly it's then less obvious which day you're referring to. — JohnLBevan
– JohnLBevan, Commented Feb 19, 2015 at 18:45

Christian Barron · Accepted Answer · 2015-02-19 18:43:57Z

1

Well for a start you have a lot of unnecessary converts so I've stripped those out. I assumed your first convert was to remove days so that's what I've turned it into.

The below should work and you don't need to use a case:

SELECT *
FROM A
WHERE  
(DATENAME(dw, GETDATE()) = 'Tuesday' and
 OrigDt BETWEEN DateAdd(Day, -3, GETDATE()) AND DATEADD(SS, -1, GETDATE())
) 
or 
(DATENAME(dw, GETDATE()) <> 'Tuesday' and
 OrigDt BETWEEN DateAdd(Day, -1, GETDATE()) AND DATEADD(SS, -1, GETDATE())
)

answered Feb 19, 2015 at 18:43

Christian Barron

2,7551 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user28455 Over a year ago

that was awesome suggestion. i didnot think about that. thanks christian.

Christian Barron Over a year ago

No problem at all @user28455, always happy to help :)

JohnLBevan · Accepted Answer · 2015-02-19 18:52:24Z

0

Try this:

SELECT *
FROM A
WHERE OrigDt BETWEEN 

    --sod yesterday or sod Sat if today's Tues
    CAST(CAST(GETDATE()-
        --adjust offset according to today's date
        CASE WHEN DATEPART(dw, GETDATE()) = 3 THEN 3 ELSE 1 END 
    AS DATE) AS DATETIME) 

    --eod yesterday 
    AND DATEADD(SECOND,-1,CAST(CAST(GETDATE()-1 AS DATE) AS DATETIME))

answered Feb 19, 2015 at 18:52

JohnLBevan

24.8k16 gold badges107 silver badges201 bronze badges

1 Comment

user28455 Over a year ago

Worked your logic, am gonna use this one. Thanks a lot John.

Collectives™ on Stack Overflow

using date between in sql where clause

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related