Date Range query not working. ASP - SQL server

I  have this query calling a SQL server where there are valid End And start dates for each news item. I want to call the items which are valid for today ie. now(). Given to understand that SQL uses the syntax GetDate()...

Here is w2hat I am using.
---------------------------------------------------------------------------------------------------------------------------------
strSQL = "SELECT NewsItem_ID, NewItem_Title FROM tblNewsItems where SubSite_ID = " & NumSiteID & " And GetDate() Is Between NewsItem_StartDate And NewsItem_EndDate;"
---------------------------------------------------------------------------------------------------------------------------------

Any advice, much appreciated.

Kind regards

Abiel m de Groot
LVL 5
Abiel de GrootDeveloperAsked:
Who is Participating?
 
Pratima PharandeCommented:
strSQL = "SELECT NewsItem_ID, NewItem_Title FROM tblNewsItems where SubSite_ID = " & NumSiteID & " And convert(varchar(10),getdate(),101)  Between convert(varchar(10),NewsItem_StartDate,101) And convert(varchar(10),NewsItem_EndDate,101);"
0
 
Abiel de GrootDeveloperAuthor Commented:
Hi pratima_mcs,

Many thanks.. that does it.

Spare me a few seconds and explain why that works. Should the dates not be comparable in their current state. What is 101?

It works, but not sure why.

Kind regards

Abbey
0
 
Pratima PharandeCommented:
go through this like for  101

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

ans syntax error in Betwen as you entered "IS"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.