Select query where condition date range not workin

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

SQL Server 2008 Forums

SQL Server Administration (2008)

Select query where condition date range not workin

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-11 : 16:57:03

I am using the following select query with a date range:

I am getting a result but the year is getting ignored. i am looking for data with 2011 year and if any records are found between the date range with previous years also are shown.

i got 2 records both records are from 2010:

Is ther any other better way to use teh date range:


SELECT  DISTINCT 
		A.ProgNO, A.ProjNO, A.ContractNO, A.RMID, A.SequenceNO, A.RMTitle, A.EnglishText as LogType, 

		A.Deleted
		FROM TAB_ccsNetRM AS A JOIN TAB_ccsNetUserAccess AS B ON (A.ProgID = B.ProgID AND A.ProjID = B.ProjID AND A.ContractID = B.ContractID)  
		Where CONVERT(varchar(10), A.DocumentDate, 101)  between '01/11/2011' and '01/22/2011' AND A.Deleted = 0

Thank you very much for the helpful info.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-11 : 17:11:43

What data type is DocumentDate?

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2011-01-11 : 17:21:18

Its a datetime datatype.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-11 : 18:00:15

change this

Where CONVERT(varchar(10), A.DocumentDate, 101)  between '01/11/2011' and '01/22/2011' AND A.Deleted = 0

to this


Where	DateAdd(day, 0, DateDiff(day, 0, A.DocumentDate), 0) between '20110111' and '20110122'
AND 	A.Deleted = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources