Date Comparision of the Month Part
-
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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply