CODEWITHSUNDEEP
sql-serverdateformatcomparisondynamics-crm-2011
user3340627
user3340627

Reputation: 3143

Date comparison doesn't take year into account

I'm trying to retrieve records by comparing date values as follows :

declare @fromdate as date
declare @todate as date
set @fromdate='2013-04-01'
set @todate='2013-04-13'
select createdon,
convert(varchar,PhoneCall.createdon,101) as createdon,
convert(varchar,@fromdate,101) as fromdate
from
PhoneCall
where convert(varchar,PhoneCall.createdon,101)  >= convert(varchar,@fromdate,101) and  convert(varchar,PhoneCall.createdon,101)  <= convert(varchar,@todate,101)

There is something wrong in the comparison, I've tried using various conversion formats but I'm not receiving correct results.

The query above has been my latest trial, although it looks correct, it doesn't take into account the comparison of the Year. So the results displayed are as follows: 

      Createdon            ConvertedCreatedOn      ConvertedFromDate  
2013-05-08 14:13:16.000      05/08/2013              05/01/2013
2014-05-01 17:10:03.000      05/01/2014              05/01/2013

EDIT: This is a query I'll use for a report. The @fromdate and @todate are report parameters that will be entered manually by the user, so I have to make sure that they're in the same format with the database date format.

Upvotes: 1

Views: 169

Answers (1)

valex
valex

Reputation: 24144

Why do you convert all DATE fields/params to VARCHAR? Just use DATE comparison:

where PhoneCall.createdon BETWEEN @fromdate AND @todate

IF you NEED to use varchar then you should use 102 format (yyyy.mm.dd) instead of 101 (mm/dd/yyyy). In this case it can be used to compare dates as strings.

Upvotes: 1

Related Questions