Compare current date with stored datetime using month an year only

Hi there.

Using SQL Server 2005 I have a field that contains a datetime value.

What I am trying to do is create 2 queries:

1. Compare to see if stored datetime is of the same month+year as current date
2. Compare to see if stored datetime is of the same year as current date

There is probably a simple solution but I keep hitting brick walls using various samples I can find, any thoughts?

Thanks in advance.

From stackoverflow

• Compare the parts of the date:

  WHERE YEAR( columnName ) = YEAR( getDate() )
  

• The datepart function lets you pull the bits you need:

  declare @d1 as datetime
  declare @d2 as datetime
  
  if datepart(yy, @d1) = datepart(yy, @d2) and datepart(mm, @d1) = datepart(mm, @d2) begin
      print 'same'
  end
  

• SELECT * FROM atable 
  WHERE 
      YEAR( adate ) = YEAR( GETDATE() )
  AND
      MONTH( adate ) = MONTH( GETDATE() )
  

• You can use something like this

  a) select * from table where MONTH(field) = MONTH(GetDATE()) and YEAR(field) = YEAR(GetDATE())

  b) select * from table where YEAR(field) = YEAR(GetDATE())

• It sounds to me like DATEDIFF is exactly what you need:

  -- #1 same month and year
  SELECT *
  FROM your_table
  WHERE DATEDIFF(month, your_column, GETDATE()) = 0
  
  -- #2 same year
  SELECT *
  FROM your_table
  WHERE DATEDIFF(year, your_column, GETDATE()) = 0
  

• While the other answers will work, they all suffer from the same problem: they apply a transformation to the column and therefore will never utilize an index on that column.

  To search the date without a transformation, you need a couple built-in functions and some math. Example below:

  --create a table to hold our example values
  create table #DateSearch
  (
      TheDate datetime not null
  )
  
  
  insert into #DateSearch (TheDate)
  --today
  select getdate()
  union all
  --a month in advance
  select dateadd(month, 1, getdate())
  union all
  --a year in advance
  select dateadd(year, 1, getdate())
  go
  
  --declare variables to make things a little easier to see
  declare @StartDate datetime, @EndDate datetime
  
  --search for "same month+year as current date"
  select @StartDate = dateadd(month, datediff(month, 0, getdate()), 0), @EndDate = dateadd(month, datediff(month, 0, getdate()) + 1, 0)
  
  select @StartDate [StartDate], @EndDate [EndDate], TheDate from #DateSearch
  where TheDate >= @StartDate and TheDate < @EndDate
  
  --search for "same year as current date"
  select @StartDate = dateadd(year, datediff(year, 0, getdate()), 0), @EndDate = dateadd(year, datediff(year, 0, getdate()) + 1, 0)
  
  select @StartDate [StartDate], @EndDate [EndDate], TheDate from #DateSearch
  where TheDate >= @StartDate and TheDate < @EndDate
  

  What the statement does to avoid the transformations, is find all values greater-than or equal-to the beginning of the current time period (month or year) AND all values less-than the beginning of the next (invalid) time period. This solves our index problem and also mitigates any issues related to 3ms rounding in the DATETIME type.

• THANKS ALOT.ITS JUST AWESUM.I LOVE THE QUERY.THANKS. IT WAS A GREAT HELP.kEEP dOING THIS