|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
datetime between problemrecords between the dates specified? declare @InstallationDateFrom datetime declare @InstallationDateTo datetime set @InstallationDateFrom = '01-01-2000 00:00:00' set @InstallationDateTo = '01-01-2007 00:00:00' select p.CompetitorMachinesSoldID as 'CompetitorMachinesSoldID', s.Competitor as 'Competitor', convert(varchar, p.InstallDate, 103) as 'InstallationDate', c.[Name] as 'CompanyName', c.CompanySiteKey as 'OwnedByID', convert(varchar, @InstallationDateFrom, 103), convert(varchar, @InstallationDateTo, 103) from CompetitorMachinesSold p inner join Competitors s on p.CompetitorID = s.CompetitorID inner join CompanySite c on c.CompanySiteKey = p.CompanySiteKey where convert(varchar, p.InstallDate, 103) between convert(varchar, @InstallationDateFrom, 103) and convert(varchar, @InstallationDateTo, 103) *** Sent via Developersdex http://www.developersdex.com *** Mike P wrote:
> Can anybody tell me why this query returns nothing when I have a load of At a guess I'd say it's because you are comparing varchars. Try> records between the dates specified? converting them all back to datetimes. Chris Lim wrote:
> At a guess I'd say it's because you are comparing varchars. Try i.e.> converting them all back to datetimes. WHERE convert(datetime, convert(varchar, p.InstallDate, 112)) BETWEEN convert(datetime, convert(varchar, @InstallationDateFrom, 112)) AND convert(datetime, convert(varchar, @InstallationDateTo, 112)) Note you will rendex any index on p.InstallDate useless by doing this. To avoid this, don't perform any operations on p.InstallDate, instead adjust @InstallationDateFrom/@InstallationDateTo to suit. e.g WHERE p.InstallDate BETWEEN convert(datetime, convert(varchar, @InstallationDateFrom, 112)) AND dateadd(ss, -1, dateadd(dd, 1, convert(datetime, convert(varchar, @InstallationDateTo, 112)))) The last convert() sets the time to 12:59:59PM in @InstallationDateTo. Chris Chris Lim wrote:
> Note you will rendex any index on p.InstallDate useless by doing this. Of course I meant RENDER any index!Chris,
This discards all data between 11:59:59 and midnight. Why would someone want to do that? It's much better to specify datetime ranges without BETWEEN, since there is no "last time" in a day: where p.InstallDate >= @InstallationDateFrom and p.InstallDate < @InstallationDateTo + 1 Steve Kass Drew University www.stevekass.com Chris Lim wrote: Show quote >Chris Lim wrote: > > >>At a guess I'd say it's because you are comparing varchars. Try >>converting them all back to datetimes. >> >> > >i.e. > >WHERE convert(datetime, convert(varchar, p.InstallDate, 112)) > BETWEEN convert(datetime, convert(varchar, @InstallationDateFrom, >112)) > AND convert(datetime, convert(varchar, @InstallationDateTo, 112)) > >Note you will rendex any index on p.InstallDate useless by doing this. >To avoid this, don't perform any operations on p.InstallDate, instead >adjust @InstallationDateFrom/@InstallationDateTo to suit. > >e.g > >WHERE p.InstallDate > BETWEEN convert(datetime, convert(varchar, @InstallationDateFrom, >112)) > AND dateadd(ss, -1, dateadd(dd, 1, convert(datetime, >convert(varchar, @InstallationDateTo, 112)))) > >The last convert() sets the time to 12:59:59PM in @InstallationDateTo. > >Chris > > > Hear hear!
BETWEEN is for numbers, where its behavior is much less ambiguous and troublesome. A Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:%235r%23dCasGHA.4252@TK2MSFTNGP02.phx.gbl... > Chris, > > This discards all data between 11:59:59 and midnight. Why would someone > want to do that? It's much better to specify datetime ranges without > BETWEEN, since there is no "last time" in a day: > > where p.InstallDate >= @InstallationDateFrom > and p.InstallDate < @InstallationDateTo + 1 > > Steve Kass > Drew University > www.stevekass.com On Thu, 27 Jul 2006 13:22:10 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@dnartreb.noraa> wrote: >Hear hear! Or datetime columns that are all zeroes in the time portion. Which> >BETWEEN is for numbers, where its behavior is much less ambiguous and >troublesome. > >A describes a LOT of datetime columns out there. Roy > Or datetime columns that are all zeroes in the time portion. Which Yeah, there is that. But >= @startdate AND < @enddate + 1 still works for > describes a LOT of datetime columns out there. those, too. So for a few extra keystrokes, you don't have to *know* whether or not all of the data in the table conforms to such a constraint, to be sure that you are collecting the data accurately. Well put!
SK Aaron Bertrand [SQL Server MVP] wrote: Show quote >>Or datetime columns that are all zeroes in the time portion. Which >>describes a LOT of datetime columns out there. >> >> > >Yeah, there is that. But >= @startdate AND < @enddate + 1 still works for >those, too. So for a few extra keystrokes, you don't have to *know* whether >or not all of the data in the table conforms to such a constraint, to be >sure that you are collecting the data accurately. > > > > Steve Kass wrote:
> Chris, Yes, fair point.> > This discards all data between 11:59:59 and midnight. Why would someone > want to do that? It's much better to specify datetime ranges without > BETWEEN, since there is no "last time" in a day: > > where p.InstallDate >= @InstallationDateFrom > and p.InstallDate < @InstallationDateTo + 1 Mike P wrote:
Show quote > Can anybody tell me why this query returns nothing when I have a load of As others have posted, the conversion to VARCHAR is your problem. It > records between the dates specified? > > declare @InstallationDateFrom datetime > declare @InstallationDateTo datetime > > set @InstallationDateFrom = '01-01-2000 00:00:00' > set @InstallationDateTo = '01-01-2007 00:00:00' > > > > select p.CompetitorMachinesSoldID as 'CompetitorMachinesSoldID', > s.Competitor as 'Competitor', > convert(varchar, p.InstallDate, 103) as 'InstallationDate', c.[Name] as > 'CompanyName', c.CompanySiteKey as 'OwnedByID', > convert(varchar, @InstallationDateFrom, 103), convert(varchar, > @InstallationDateTo, 103) > from CompetitorMachinesSold p > inner join Competitors s > on p.CompetitorID = s.CompetitorID > inner join CompanySite c > on c.CompanySiteKey = p.CompanySiteKey > where convert(varchar, p.InstallDate, 103) > between convert(varchar, @InstallationDateFrom, 103) > and convert(varchar, @InstallationDateTo, 103) > > > > *** Sent via Developersdex http://www.developersdex.com *** looks like you're trying to find all install dates between 1/1/2000 and 1/1/2007, inclusive. Instead of doing ANY conversion at all, just change the value of @InstallationDateTo to '1/2/2007 00:00:00'.  |
|||||||||||||||||||||||
Other interesting topics