You are here
Home > Sql server > Sql server 2016 > SQL: Date and Time Functions

SQL: Date and Time Functions

Date and Time Functions

In this post we will discuss date and time functions in SQL server 2016. SQL support many date and time functions that are used to manipulate date and time.

DATENAME():

  • DATENAME returns a character name string that represents the specific datepart of the date.
  • Syntax:

DATENAME(datepart, date)

  • The first argument is the date part that you want to return from second argument.
  • It is language dependent.
  • Date part must be one of the below.

Name

Datepart argument
Year

yy, yyyy

Month

mm, m
Day of year

dd, d

Week

wk, ww
Weekday

dw, w

Hour

hh
Minute

mi, m

Second

ss, s
Quarter

qq, q

Example:

DECLARE @date_ex date=’20171106′;

select DATENAME(mm,@date_ex) as monthofyear;

output:

monthofyear

November

DATEADD():                                                                          

  • DATEADD() function returns a specified date with the specified number added to the specific date part of that date.
  • Syntax:

DATEADD(datepart, number, date)

Example:

select DATEADD(dd,1,GETDATE()) as add1todate;

output:

add1todate

2017-11-07 13:15:00.847

DATEDIFF():

  • DATEDIFF() returns the count of the startdate and enddate. It returns a value of INT type.
  • This function looks only at the parts from the requested one and above in the date and time not below.
  • Syntax:

DATEDIFF(datepart, startdate, enddate)

Example:

select DATEDIFF (yy,’1995-09-28 01:01:01.1234567′,’2016-09-28 01:01:01.1234567′) as datediffexam;

output:

datediffexam

21

DAY():

  • Returns an integer representing the day(day of month).
  • Syntax:

DAY(date)

Example:

select DAY(‘2017-03-07’) as exday;

output:

exday

7

MONTH():

  • Returns an integer that represents the month of the specified date.
  • Syntax:

MONTH(date)
Example:
select MONTH(‘2017-03-07’) as exmonth;
output:

exmonth

3

YEAR():

  • Returns an integer that represents the year of the specified date.
  • Syntax:

YEAR(date)
Example:
select YEAR(‘2017-03-07’) as exyear;
output:

exyear

2017

EOMONTH():

  • Return the last day of the month that contains the specified date.
  • Syntax:

EOMONTH(date[,month_to_add])

  • The first argument is the input date to return the last day of the month. The second argument is the optional it specifies the number to be added or minus from the input date month.

Example:
select EOMONTH(GETDATE()) as lastday;
output:

lastday

2017-11-30

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Microsoft SQL 70-762 Practice Test.

Microsoft MCSA 70-761 Practice Test:

Leave a Reply

Top