MS SQL Tips & Tricks

How to build up datetime type having year, month, day, hour, minute and second

Building time part of datetime type 

declare @Hour int;   set @Hour = 12; 
declare @Minute int; set @Minute =13;
declare @Second int; set @Second =14;

select dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0)

1900-01-01 12:13:14.000

Building date part of datetime type 

declare @Year int;   set @Year  = 2009; 
declare @Month int;	 set @Month = 5;
declare @Day int;	 set @Day   = 15;

select dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)

2009-05-15 00:00:00.000

...and all together. Date and time the same time 

declare @Year int;   set @Year  = 2009; 
declare @Month int;	 set @Month = 5;
declare @Day int;	 set @Day   = 15;
declare @Hour int;   set @Hour = 12; 
declare @Minute int; set @Minute =13;
declare @Second int; set @Second =14;

select dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)+
	   dateadd(ss,(@Hour*3600)+(@Minute*60)+@Second,0);

2009-05-15 12:13:14.000

See also


Learn more tricks

Add Comment

Comments