Compare current date with stored datetime using month an year only

Question

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.

Answer 1

Compare the parts of the date:

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

Answer 2

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

Answer 3

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

Answer 4

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())

Answer 5

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

Answer 6

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.

Answer 7

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