sql function help

Question

Hi I need help about writing function in SQL that will return me a period of how long employee is working in a firm in format yy/mm/dd based on date of when employee started to work in format yyyy/mm/dd. Let's say that one employee start at 1981/04/21 function should return me 33/01/01 this employee is working 33 years one month and one day. I don't need to pay attention on leap years i could take that every month have 30 days. Thanks

sudipta sanyal · Answer

CREATE FUNCTION dbo.fn_date_diff (@in_start_date DATETIME) RETURNS VARCHAR(200) AS BEGIN DECLARE @l_time_period VARCHAR(200) @l_year INT @l_month INT@l_date INT SELECT @l_year = DATEDIFF(year @in_start_date GETDATE()) SELECT @l_month = DATEDIFF(month @in_start_date GETDATE()) - (datediff(year@in_start_dateGETDATE()) * 12) SELECT @l_date = DATEPART(d GETDATE())- DATEPART(d@in_start_date) SELECT @l_time_period = CONVERT(VARCHAR(10)@l_year) + '/' + CASE WHEN LEN (@l_month) = 1 THEN '0' + CONVERT(VARCHAR(10)@l_month) ELSE CONVERT(VARCHAR(10)@l_month) END +'/' + CASE WHEN LEN (@l_date) = 1 THEN '0' + CONVERT(VARCHAR(10)@l_date) ELSE CONVERT(VARCHAR(10)@l_date) EN RETURN @l_time_period END GO SELECT dbo.fn_date_diff('1981-04-21')

sudipta sanyal · Answer

hi dragan Please Mark as Accepted Answer if it helps you.

dragan perkovic · Answer

Thanks sudipta sanyal  it's what I need. You saved my time. Thank's again

Anupam Singh · Answer

Hi You can use DATEDIFF function in sql. it gives you result in daysmonthsminutes and many more see example here : http://www.w3schools.com/sql/func_datediff.asp H ope will help.

Jignesh Trivedi · Answer

hi please refer below article it give you idea to find year month and days from two different date. http://www.codeproject.com/Tips/519083/Get-Duration-Between-Two-Dates-in-Years-Months-Day http://www.c-sharpcorner.com/UploadFile/rohatash/calculating-age-in-years-months-and-days-in-sql-server-2012/ hope this will help you