MS SQL Tips & Tricks

How to replace date part in datetime variable

The simpliest way of replacing the date in datetime variable without changeing the actual time is to do a trick as bellow: 

declare @cDateTime datetime;
declare @cYear integer;
declare @cMonth integer;
declare @cDay integer;

set @cDateTime=getdate();
set @cYear = 2009;
set @cMonth = 1;
set @cDay = 1;

select  (@cDateTime-
	dateadd(dd,0, datediff(dd,0,@cDateTime)))+
	dateadd(month,((@cYear-1900)*12)+@cMonth-1,@cDay-1);

See also


    Learn more tricks

    Add Comment

    Comments