SQL Server Developer Center >
SQL Server Forums
>
SQL Server Database Engine
>
Filter date on a view
Filter date on a view
- Hi, I have a problem in a view. I try do do a simple view with a filter on a datetime field. The filter is very simple >= 01/0/2007 and <01/01/2009. SQL Server give me back no rows in this view and this is wrong. I have tried to do it in access and if I use linked tables it still doesn't work but if I copy the table in the access database it works perfectly. One of you has some ideas??
Thanks Francesco
Answers
- Have a look at this blog post for a good discussion of this topic: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx
Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)- Marked As Answer byFrancesco Vanin Wednesday, November 04, 2009 1:24 PM
All Replies
- Try using BETWEEN instead....
Tony C - Don't use BETWEEN, there are well documented issues with fringe cases.
It's most likely to do with how you are specifying the dats in your WHERE clause. YYYYMMDD is the format guaranteed to work regardless of regional settings. You also need to wrap you date with single quotes, otherwise 01/01/2009 is evaluated as anumeric operation i.e. 1 divided by 1 divided by 2009
Try this:
WHERE column >= '20070101' AND column < '20090101'
Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)- Edited byAdam Tappis Wednesday, November 04, 2009 11:46 AM
- Have a look at this blog post for a good discussion of this topic: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx
Adam Tappis. (MCSD VB6 & .NET, MCDBA SQL 2000)- Marked As Answer byFrancesco Vanin Wednesday, November 04, 2009 1:24 PM
- Adam thanks for your link! I have had a user language problem! now I have resolved!!
Thanks for your help guys! I have really apreciated it!
Francesco
