Date Criteria in View not filtering

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Criteria in View not filtering

Author

Topic

lbunch
Starting Member

9 Posts

Posted - 2007-03-19 : 10:23:02

I am using BETWEEN '02/01/2007' AND '2/01/2006' in the criteria of a VIEW and have tried <= '02/01/2007' AND >='2/01/2006' but both are not filtering the dates correctly. Is there another way? What am I missing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-19 : 10:27:42

Are the date columns in the underlying tables stored as VARCHAR?

If so, you have no other options than this

SELECT	*
FROM	View
WHERE	CAST(DateCol AS DATETIME) BETWEEN CAST('20060201' AS DATETIME) AND CAST('20070201' AS DATETIME)

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-19 : 10:27:54

You are going to have to supply some more info, like the table ddl, the view and sample data. Whya are you saying it's not working?

Oh, and btw, look at the the between...isn't that backwards?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-19 : 10:28:35

1. for BETWEEN, the 1st date should be the smaller date and the 2nd larger
2. use universal format YYYYMMDD and not ambiguous MM/DD/YYYY or DD/MM/YYYY. These are language dependent


BETWEEN '20060102' AND '20070102'


     >= '20060102'
AND  <= '20070102'

lbunch
Starting Member

9 Posts

Posted - 2007-03-19 : 11:58:23

Thank you - I used Peter's suggestion.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources