Start Date and End Date Question in SQL 2005 1

[jjc3397]

I have a table called Tranfers with a CDINDATE. I need the number of records pulled from this table from 04/01/2007 to 04/30/2007. I have used the coding below but come up with 15 records and the records contain dates 03/27/07 which is not in my range. I have used an ACCESS Query and coming up with two records for this time period and both records are CDINDATES of 4/20/2007. TWO records is correct. But I want the same result in the SQL 2005

My coding:

SELECT dbo.License.Type, dbo.Transfers.[CDIN-TYPE], dbo.License.Number, dbo.Transfers.[CDIN-NUM], dbo.Transfers.CDINDATE, dbo.License.County,
dbo.County.[County Name], dbo.License.Name, dbo.License.Trade_Name, dbo.License.Address, dbo.License.Zip, dbo.License.Location1,
dbo.License.Rec_Status
FROM dbo.License INNER JOIN
dbo.Transfers ON dbo.License.Type = dbo.Transfers.[CDIN-TYPE] AND dbo.License.Number = dbo.Transfers.[CDIN-NUM] INNER JOIN
dbo.County ON dbo.License.County = dbo.County.County
WHERE (dbo.License.Rec_Status = 'a') AND (dbo.License.County = 0) OR
(dbo.License.County = 4) OR
(dbo.License.County = 32) OR
(dbo.License.County = 33) OR
(dbo.License.County = 53) OR
(dbo.License.County = 54) OR
(dbo.License.County = 63) OR
(dbo.License.County = 00) AND (dbo.Transfers.CDINDATE >= 4 / 1 / 2007) AND (dbo.Transfers.CDINDATE <= 4 / 30 / 2007)

Somehow, the Coding is pulling dates I do not need and it has to do with the way I have worded the dates.

I need some help.

jjc3397

 

[SQLDenis]

(dbo.License.County = 00) AND (dbo.Transfers.CDINDATE >= 4 / 1 / 2007) AND (dbo.Transfers.CDINDATE <= 4 / 30 / 2007)
has to be
(dbo.License.County = 00) AND (dbo.Transfers.CDINDATE >= '20070401') AND (dbo.Transfers.CDINDATE <= '20070430')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[jjc3397]

Dennis,

I get the same result with your code.

jjc3397

 

[SQLDenis]

probably a missing parenthese somewhere with all the ORs try this as your WHERE clause

WHERE (dbo.License.Rec_Status = 'a')
AND dbo.License.County in( 0,4,32,33,53,54,63)
AND dbo.Transfers.CDINDATE >= '20070401'
AND dbo.Transfers.CDINDATE <= '20070430'


which makes it a little easier to debug also

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[jjc3397]

Thanks, Dennis

That worked! I got two records with a date of 4/20/2007.

jjc3397