Avatar of Tom_Hickerson
Tom_HickersonFlag for United States of America

asked on 

SQL statement date between not working

I have data in that all has the date set to sometime november and december of this year, and when I run the following SQL statement.  I don't get any values from returned

select piece_type,witewood_proof,heartwood_proof,count(*) from proof where date  between '1/20/2003 00:00:000' and '10/29/2007 00:00:000'
 group by piece_type,witewood_proof,heartwood_proof

However, if I change the statement so that it goes to '12/29/2007' I do get values from this year returned

select piece_type,witewood_proof,heartwood_proof,count(*) from proof where date  between '1/20/2003 00:00:000' and '12/29/2007 00:00:000'
 group by piece_type,witewood_proof,heartwood_proof


Does it have anything to do with the table entry being called date instead of something like myDate?

Thanks,
Tom
Microsoft SQL Server

Avatar of undefined
Last Comment
adwiseman
ASKER CERTIFIED SOLUTION
Avatar of adwiseman
adwiseman

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Tom_Hickerson
Tom_Hickerson
Flag of United States of America image

ASKER


No, not cut and paste error.  When I use "10" as a month in the first example I get no results.  When I use "12" as a month in the second example I get results.  

Your second comment seems to be the source of the problem.  It was [Date], but it was set to char.  I changed it to datetime, and now everything works.

Thanks,
Tom
Avatar of adwiseman
adwiseman

because [date] was a char field, it was being evaluated as ASC Text.  Like placing the dates in Alphabetical Order.

One more thing to note.  "Between" is inclusive.  like date >= 'date1' AND date <= 'date2'.  In your example.  12:00 AM on date1 to 12:00 am on date2 would not return any records on date2 that didn't happen at 12:00am or that did not have a time in the date field.  I'm guessing that your dates don't have a time on them, but it's something I've overlooked more than once.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo