Updating year in datetime variable

sql-server datetime datediff
JGreasley picture JGreasley · May 11, 2012 · Viewed 8.2k times · Source

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 

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.

Thanks

Answer

AdaTheDev picture AdaTheDev · May 11, 2012 
DECLARE @date datetime = '2007-01-09T12:34:56'
SELECT @date = DATEADD(yyyy, DATEDIFF(yyyy, @date, GETDATE()), @date)

SELECT @date

Related questions

how to get the number on months between two dates in sql server 2005

I have a column in my sql server 2005 table that should hold the number of months an employee has been in service. Since I also have the date the employee was engaged, I want the "months_In_Service" column to …

Read More
How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More