Using in And Between

Question

SELECT TOP 5 Notices.Id, NoticeL.Notices_Id, Loc.Id as Location_Id,
CAST(Notices.Text AS TEXT) as Text, CAST(Notices.Title AS TEXT) as Title,
Notices.CDate as RegDate 

FROM NoticeL JOIN Notices ON NoticeL.Notices_Id=Notices.Id
JOIN Loc ON NoticeL.Loc_Id=Loc.Id
WHERE Loc_Id IN (1) BETWEEN '06/04/2012' AND '23/04/2012'

I am trying to use between following IN, but I am having no luck. I am getting the following syntax error:

   Msg 156, Level 15, State 1, Line 1
   Incorrect syntax near the keyword 'BETWEEN'.

I guess MsSql does not like the syntax. How can I do this?

Second question, I would like to filter the notices out by the last 2 weeks. Is there anyway I can do this dynamically in mssql. Thanks for your help.

do you want that test on the same column? you did not use that as a different condition.. maybe it's something like ...`Loc_Id IN (1) AND Loc_Id BETWEEN`... — mishu, Apr 06 '12 at 14:49
That seems to have done the trick thanks mishu, does mssql have a function to only filter out last 2 weeks worth of notices? — bobo2000, Apr 06 '12 at 14:54

score 2 · Accepted Answer · edited May 23 '17 at 11:49

 SELECT TOP 5 Notices.Id,
              NoticeL.Notices_Id, 
              Loc.Id as Location_Id,
              CAST(Notices.Text AS TEXT) as Text,
              CAST(Notices.Title AS TEXT) as Title,
              Notices.CDate as RegDate
 FROM NoticeL 
 JOIN Notices ON NoticeL.Notices_Id=Notices.Id JOIN Loc ON NoticeL.Loc_Id=Loc.Id
 WHERE Loc_Id IN (1) 
 AND Notices.CDate BETWEEN '06/04/2012' AND '23/04/2012'

You can't combine IN and BETWEEN on the same field. I'm guessing the Between needed to be used on Notices.CDate since that seems to be the only date field.

If you want to grab the last two weeks worth then the last line changes to

 AND Notices.CDate BETWEEN GETDATE() - 14 AND GETDATE()

If time matters in your query then you might want to do something to strip the time off of GETDATE(). This question seem to have some good answers.

How to return the date part only from a SQL Server datetime datatype

score 2 · Answer 2 · answered Apr 06 '12 at 14:54

NoticeL.Loc_Id=Loc.Id WHERE Loc_Id = 1 -- IN (1) should work too
                                      -- if you're building the query 
                                      -- dynamically as a string and want 
                                      -- to use IN with a list of IDs 
AND Notices.CDate BETWEEN '06/04/2012' AND '23/04/2012

for the last two weeks (meaning the last 14 calendar days) you could do

AND Notices.CDate BETWEEN DATEADD(d,-14,GETDATE()) AND GETDATE()

score 2 · Answer 3 · answered Apr 06 '12 at 14:57

2

I think you are missing the AND, try this

FROM NoticeL JOIN Notices ON NoticeL.Notices_Id=Notices.Id
JOIN Loc ON NoticeL.Loc_Id=Loc.Id
WHERE Loc_Id IN (1) AND BETWEEN '06/04/2012' AND '23/04/2012'

answered Apr 06 '12 at 14:57

Guruparan

71
1
8

1

...and the column name too, yes? – Apr 06 '12 at 15:00

score 0 · Answer 4 · answered Apr 06 '12 at 14:55

Try this one

    SELECT TOP 5 Notices.Id, NoticeL.Notices_Id, Loc.Id as Location_Id,
CAST(Notices.Text AS TEXT) as Text, CAST(Notices.Title AS TEXT) as Title,
Notices.CDate as RegDate 

FROM NoticeL JOIN Notices ON NoticeL.Notices_Id=Notices.Id
JOIN Loc ON NoticeL.Loc_Id=Loc.Id
WHERE Loc_Id IN (1) HAVING RegDate BETWEEN '06/04/2012' AND '23/04/2012'

Using in And Between

4 Answers4