Date Comparision of the Month Part
-
Hi Everyone,
I have a need for a query that compares the date but I only need to compare the Month and Year parts. I Don't want the day to be considered during the comparision. Can someone give me some tips for this. It appears that the smalldatetime and DateTime datatypes will only except a date that contains the day part. I would like to do something like this in a where clause:
where CompareDate = '10/2004'
I have considered just putting it in a string, but I'm worried it will not compare right. If we have two dates:
5/2004 and 5/2005 as a string (varchar or char)
How will it look at the above? Does anyone out there do what I need to do. What would be your recomendations. Thanks for any help that you could provide.
Michael Lee
-
Why not WHERE DATEPART(MONTH, CompareDate1) = DATEPART(MONTH, CompareDate2) AND DATEPART(YEAR, CompareDate1) = DATEPART(YEAR, CompareDate2)
? Of course the varchar > varchar compare would be better execution-wise but the above code should work also
Good Hunting!AJ Ahrens
webmaster@kritter.net -
If I see this right, you want to query for a specific month, right?
If so, why extract dateparts and convert anyway? You could also query for something like this:
WHERE
< your date > >= DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE())-2,30)+1
AND
< your date > < DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE())-1,30) +1
That example above would query the previous month. You might need to tweak this for your needs. But this way you can use efficient integer operations on datetime columns and indexes could be used, thus performance should be rather good.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Still another way to consider:
-- This expression: CONVERT(varchar(6), <datetime value>, 112)
-- Returns: YYYYMM as a character string that will sort/compare correctly.
DECLARE @date1 datetime, @date2 datetime
SET @date1 = '5/1/2004'
SET @date2 = '5/25/2004'
IF CONVERT(varchar(6), @date1, 112) = CONVERT(varchar(6), @date2, 112)
PRINT 'Same year/Month'
ELSE
PRINT 'Different Year/Month'
-- another example
SET @date1 = '5/1/2004'
SET @date2 = '4/25/2004'
IF CONVERT(varchar(6), @date1, 112) = CONVERT(varchar(6), @date2, 112)
PRINT 'Same year/Month'
ELSE IF CONVERT(varchar(6), @date1, 112) < CONVERT(varchar(6), @date2, 112)
PRINT 'Date1 less than date2'
ELSE
PRINT 'Date2 less than date1'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply