Date Functions in SQL :-

Getdate () Function In Sql Server :-

  • This function returns correct date
  •   Example  :-select getdate ( )

Year ( ) Function In Sql Server :-

  • Returns year part of the date

Syntax  :-year (date)

Example  :- Select year(getdate())

Display employee records joined in last year.

Select * from emp where year (hiredate)%4=0

Month ( )  Function In Sql Server :-

  • Returns month part from date.

Select month(getdate())

Display employee records join between january & April.

Day ( ) Function In Sql Server  :-

  • Returns day part from the date

Syntax  :- day(date)

Example  :-

Display employe records who are joined in first 15 days of January month inyear 1981.

 

DatePart ( ) Function In Sql Server  :-

  • It extracts part of the date

Syntax :- datepart(interval,date)

Example  :-Select date part (yy,getdate())

Output  :- 2009

Different intervals are :- 

  • dd
  • mm
  • yy
  • hh—(hours)
  • mi—(minutes)
  • ss     (seconds)
  • dw    (day after week)

DateName ( ) Function In Sql Server  :-

  • Syntax    :  Date name(interval,date)

                Interval  :-mm→october

                dw→Friday

  • Example :-    Select datename(mm,setdate())

Output → october

  • Display ‘smith joined on Sunday like  :-

   Select ename +joined on +datename(dw,hiredate) from emp

DATEADD  ( )  Function In Sql Server  :-

  • To add no. of months days years to a particular date.

Syntax: dateadd (interval,number,date)

Example  :-  Select dateadd (dd,10,getdata())

Date Diff ( ) Function In Sql Server  :-

  • It returns difference b/w two dates.

Example  :-    date diff (interval, d1, d2)

Example  :-   Select date diff(dd, getdata( ), 23-oct-2010)

                ↓

Output:-   365