You are here

SQL : Date and Time Functions

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.

GETDATE():

  • GETDATE() function returns the current date and time in the SQL server instance you are connected in the DATETIME data type.
  • GETDATE() function is not SQL standard.
  • Syntax:

GETDATE()
Example:
select GETDATE() as todaydate;
output:

todaydate

2017-11-06 18:22:52.263

SYSDATETIME():

  • Returns a DATETIME2 value that contains the date and time of the computer on which the SQL server is running.
  • Syntax:

SYSDATETIME()
Example:
select SYSDATETIME() as todaydate;
output:

todaydate

2017-11-06 18:26:19.1213706

SYSUTCDATETIME():

  • Returns a DATETIME value of the computer on which SQL server is running.
  • The date and time is returned as UTC(Coordinated Universal Time).
  • Syntax:

SYSUTCDATETIME()
Example:
select SYSUTCDATETIME() as utcdate;
output:

utcdate

2017-11-06 13:06:11.7814437

DATEPART():

  • Returns an integer that represent the specified datepart of the specific date.
  • Using DATEPART() function we can extract date or year or month from any given date.
  • Syntax:

DATEPART()

  • 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:
select DATEPART(ss,GETDATE()) as exsecond;
output:

exsecond

8

DATEFROMPARTS():

  • Returns a date value for the specified year, month, day.
  • Syntax:

DATEFROMPARTS(year, month, day)

Example:
select DATEFROMPARTS(2017,11,06) as datecreated;
output:

datecreated

2017-11-06

TIMEFROMPARTS():

  • Returns a time value for the specified time and with the specified precision.
  • Syntax:

TIMEFROMPARTS(hour, minute, second, fraction, precision)
Example:
select TIMEFROMPARTS(06,56,55,2,7) as timecreated;
output:

timecreated

06:56:55.0000002

SYSDATETIMEOFFSET()

  • Returns a datetimeoffset(7) value that contains the date and time of the computer on which SQL is running.
  • The time zone offset is included.
  • Syntax:

SYSDATETIMEOFFSET()
Example:
select SYSDATETIMEOFFSET() as dateoffset;
output:

dateoffset

2017-11-06 18:59:40.4292903 +05: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