sql - how view result date time datediff dateadd , but out-of-range value -
i try compare date , start date working master_employee. failed...
if @ line write
select @date = date_start master_employee id = '2' its succes. hope, can view result in table master_employee.
can me ? thank's much..
declare @date datetime ,@tmpdate datetime ,@years int ,@months int ,@days int select @date = date_start master_employee select @tmpdate = @date select @years = datediff(yyyy, @tmpdate, getdate()) - case when (month(@date) > month(getdate())) or ( month(@date) = month(getdate()) , day(@date) > day(getdate()) ) 1 else 0 end select @tmpdate = dateadd(yyyy, @years, @tmpdate) select @months = datediff(mm, @tmpdate, getdate()) - case when day(@date) > day(getdate()) 1 else 0 end select @tmpdate = dateadd(mm, @months, @tmpdate) select @days = datediff(dd, @tmpdate, getdate()) select @years years ,@months months ,@days dayss ,getdate() date_now
this give how many days, months, years have passed in aggregate employees, far can tell tying do.
declare @today datetime = convert(date,getdate()) select sum(datediff(day,isnull(convert(datetime,@date),today),@today)) [days] ,sum(datediff(month,isnull(convert(datetime,@date),today),@today)) [months] ,sum(datediff(year,isnull(convert(datetime,@date),today(,@today)) [years] master_employee the reason
select @date = date_start master_employee is failing because trying assign start dates same variable.
if want separate lines each employee try:
declare @today datetime = convert(date,getdate()) select id ,sum(datediff(day,isnull(convert(datetime,@date),today),@today)) [days] ,sum(datediff(month,isnull(convert(datetime,@date),today),@today)) [months] ,sum(datediff(year,isnull(convert(datetime,@date),today),@today)) [years] master_employee group id be careful, month , year can misleading, if person started 12/31/14 , ran on 1/1/15 see 1 day, 1 month, 1 year. might better off using days , figuring own math how long is...
Comments
Post a Comment