Datediff Function Sql Server

DateDiff function returns the  difference between to dates

Suppose we have employee table and it has following rows and columns

Id	Name	Description	JoiningDate
1	Test1	Executive	10/5/2013 0:00
2	Test2	S.Executive	10/5/2012 0:00
3	Test3	Team Lead	10/5/2012 0:00
4	Test4	Jr Engineer	10/11/2014 0:00

Calculate the Employee joining Year, month and days

  select name, datediff(year,JoiningDate,getdate()) Year  , datediff(year,JoiningDate,getdate()) month ,datediff(DAY,JoiningDate,getdate()) day from Employee  where joiningdate is not null

Calculate the age   Sql server in years-month and days

  Declare @dateofbirth datetime

  Declare @years int

  declare @months int

  declare @days int

  declare @temp date

  set @dateofbirth='1983-04-27';

  set @temp  =@dateofbirth

  set @years = (select DATEDIFF(yy, @temp, GETDATE())- CASE WHEN (MONTH(@dateofbirth) > MONTH(GETDATE())) OR (MONTH(@dateofbirth) = MONTH(GETDATE()) AND DAY(@dateofbirth) > DAY(GETDATE())) THEN 1 ELSE 0 END)

SET @temp = DATEADD(yy, @years, @temp)

SET @months = DATEDIFF(m, @temp, GETDATE()) - CASE WHEN DAY(@dateofbirth) > DAY(GETDATE()) THEN 1 ELSE 0 END

SET @temp = DATEADD(m, @months, @temp)

SET @days = DATEDIFF(d, @temp, GETDATE())

select @years as TotalYear ,@months as M

Sql server Get the Day of week

Suppose we have employee table and it has following rows and columns

Id	Name	Description	JoiningDate
1	Test1	Executive	10/5/2013 0:00
2	Test2	S.Executive	10/5/2012 0:00
3	Test3	Team Lead	10/5/2012 0:00
4	Test4	Jr Engineer	10/11/2014 0:00

select  case   DATEPART(DW,getdate())+@@DATEFIRST % 7  WHEN 1 THEN 'SUNDAY'

    WHEN 2 THEN 'MONDAY'

    WHEN 3 THEN 'TUESDAY'

    WHEN 4 THEN 'WENSDAY'

    WHEN 5 THEN 'THURSDAY'

    WHEN 6 THEN 'FRIDAY'

    WHEN 7 THEN 'SATURDAY'

END

DATEPART() Function in Sql Server

DATPART function is used to return single part such as date, time, year, hour, minute seconds. It’s very useful function

datepart(datepartstring,date)

Here is common queries we have used in sql server day by day operations

Suppose we have employee table and it has following rows and columns

Id	Name	Description	JoiningDate
1	Test1	Executive	10/5/2013 0:00
2	Test2	S.Executive	10/5/2012 0:00
3	Test3	Team Lead	10/5/2012 0:00
4	Test4	Jr Engineer	10/11/2014 0:00

1)  Get Year from joining date of employee

    select name, DATEPART(year,joiningDate)  from employee

2)  Get Month from joining date of employee

select name ,DATEPART(month,joiningDate) from Employee

3)  Count employees based upon year

  select name ,DATEPART(YEAR,joiningDate) from Employee where DATEPART(YEAR,joiningDate)='2013'

4)  Get joining day of Employee

   select name , case   DATEPART(DW,JoiningDate)+@@DATEFIRST % 7  WHEN 1 THEN 'SUNDAY'

    WHEN 2 THEN 'MONDAY'

    WHEN 3 THEN 'TUESDAY'

    WHEN 4 THEN 'WENSDAY'

    WHEN 5 THEN 'THURSDAY'

    WHEN 6 THEN 'FRIDAY'

    WHEN 7 THEN 'SATURDAY'

END  from Employee where JoiningDate is not null