CODEWITHSUNDEEP
t-sqlsql-server-2005datedatetimecomparison
Albert Laure
Albert Laure

Reputation: 1722

What would be the best way to compare SQL DATE using months and year

Good Day Everyone,

Today I'am facing some problems about Date Comparison, i have Set of Data with SQL Date Time with Them

Sample Data

If i want to retrieve all the ID's from a Specific Month and Specific Year

(ex. All ID's that has a Date of Sepetember 2013)

i use this kind of code in the where clause

(DATENAME(MONTH,PHI.fld_LoanDate)) =  (DATENAME(MONTH,@month))
    AND
(DATEPART(YEAR,PHI.fld_LoanDate)) = (DATEPART(YEAR,@year))

but as of now i'am encountering some bugs maybe because of the Ugly comparisons in Date

so i would like to ask some help on how would i improve that code or if you can give me a better way to compare date using month and Year

Thanks :)

**EDIT: Month and Year are given by the user, and there are also some queries that i use like this

(DATENAME(MONTH,PHI.fld_LoanDate)) =  (DATENAME(MONTH,DATEADD (MONTH,-4,@month + '2000')))  
  AND
    (DATEPART(YEAR,PHI.fld_LoanDate)) = (DATEPART(YEAR,@YEAR1))

Because i need to Subtract some months in a specific date that was given by the USer.

any better way would be Highly appreciated :)

Upvotes: 0

Views: 2337

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

It would be better to just use a range query:

PHI.fld_LoanDate >= '20130901' and
PHI.fld_LoanDate < '20131001'

In this way, if there's potentially an index on fld_LoanDate it could be used by the above query.

Having seen edits - even if the values are supplied by the user, it's easy enough to compute a start and end date for the above comparison:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = DATEADD(year,@Year - 1900,DATEADD(month,@Month - 1,'19000101'))
SET @EndDate = DATEADD(month,1,@StartDate)

and then use:

PHI.fld_LoanDate >= @StartDate and
PHI.fld_LoanDate < @EndDate

I'm not sure why you're obsessed with comparing date values by name but it's unlikely to be the best way to do things.

One final trick if the above are still not sufficient. This one won't allow you to use an index but does simplify some processes - you can do:

DATEADD(month,DATEDIFF(month,0,PHI.fld_LoanDate),0)

Which rounds the date in fld_LoanDate down to the nearest 1st of the month.

Upvotes: 3

Related Questions