SQL Server query - where with IN operator and Getdate()

Question

I have below an SQL query with getdate inside IN as shown in example

Since IN requires expression in quotes it will not work.

It will work with dynamic query, but is there any other way other than dynamic query ?

e.g.

PIVOT
(
SUM(Items)
FOR [ReceivedDate]
IN (select  
    CONVERT(char(10), GetDate(),126)
,CONVERT(char(10), GetDate()-1,126) 
      ,CONVERT(char(10), GetDate()-1,126) 
)

This is not working.

appears to be a duplicate of - http://stackoverflow.com/questions/6060526/sql-pivot-select-from-list-in-select — attila, Mar 27 '14 at 15:04

score 0 · Answer 1 · answered Mar 27 '14 at 05:41

0

This the sample for "IN" statement

SELECT * FROM Customers WHERE City IN ('Paris','London');

Try some thing like this

Where itemDate IN (SELECT getdate()-1, getdate-2,..)

answered Mar 27 '14 at 05:41

Naveenkumar

483
5
20

Not working Naveen, edited my question with an example. – Wilz Mar 27 '14 at 06:07

score 0 · Answer 2 · answered Mar 27 '14 at 05:45

IN doesn't require items to be specified in quotes. What are you trying to do exactly?

If you're trying to compare dates and are not getting results then perhaps it's because you need to strip off the time component.

SELECT 
    GetDate()                                         AS RightNow,
    Cast(Floor(Cast(GetDate() AS float)) AS datetime) AS DateOnly

attila · Answer 3 · 2014-03-27T15:07:21.367

0

Sql server's IN basically just expands the criteria into ORs.

What you have works and I put together a sqlfiddle to show it. http://sqlfiddle.com/#!3/ffcbd/3/0

UPDATE - since the OP originally did not include this was in reference to PIVOT, my answer above assumed it was the IN logical operator. It appears the answer to the question, is "NO" if dynamic sql is not an option.

edited Mar 27 '14 at 15:07

answered Mar 27 '14 at 06:00

attila

2,219
1
11
15

SQL Server query - where with IN operator and Getdate()

3 Answers3