Updating year in datetime variable

I’m playing around with manipulating a datetime variable. I can’t seem to find a way to update a datetime variable year to the current year.

For example I have

  • How can I check if a View exists in a Database?
  • How can I append a column in SQL?
  • Passing xml string parameter to SQL Server stored procedure
  • SQL Server get column not in Group By clause?
  • In SQL Server 2008 R2 script data missing on Script Wizard
  • dbo.TempGetStateItemExclusive3 called repeatedly
  • 2007-12-01 00:00:00.000

    But I would like that to be

    2012-12-01 00:00:00.000    (The current year were in)

    I’ve been playing with datediff, but I can’t seem to nail it.

    Any advice would be appreciated.


    3 Solutions collect form web for “Updating year in datetime variable”

    DECLARE @date datetime = '2007-01-09T12:34:56'
    SELECT @date = DATEADD(yyyy, DATEDIFF(yyyy, @date, GETDATE()), @date)
    SELECT @date

    Maybe something like this:

    For sql server 2008+

    DECLARE @date DATETIME='2007-12-01 00:00:00.000'
    SET @date=DATEADD(year,DATEDIFF(year,@date,GETDATE()),@date)

    For sql server 2005

    SET @date='2007-12-01 00:00:00.000'
    SET @date=DATEADD(year,DATEDIFF(year,@date,GETDATE()),@date)

    Here’s a simple way:

    select @yourDate = dateadd(year, datepart(year, getdate()) - datepart(year, @yourDate), @yourDate)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.