DATETIME in build functions IN SQL SERVER to work with DateTime

There are several date time data types present in SQL server.

  • Time
  • date
  • datetime
  • smalldatetime
  • datetime2
  • datetimeoffset

 

Time

When we require only the time in the application we can make use of time data type. Time data type consumes 3-5 bytes of memory.

 

Date

When we want to store only the Date we can make use of Date data type. Date data type uses 3 bytes of memory.

 

DateTime

When we want both the date and time to be stored in database we can make use of datetime data type. DateTime data type uses 8 bytes of memory,

 

SmallDateTime

Small datetime stores the datetime. The time is based on 24 hours per day with seconds always: 00

 

DateTime2

Datetime2 is extension of datetime that can have larger date range

 

DateTimeOffset

DateTimeOffset is a datetime with UTC offset. YYYY-MM-DD hh:mm:ss[.nnnnnnn][+|-]hh:mm

 

UTC: Coordinated Universal Time is a standard that is used across the world based on which world regulates the clock and time. UTC is basically world standard time and based on this time other country time is calculated.

 

 

 

Date and Time Function in SQL server

 

Current TimeStamp:

Returns the db system timestamp without the db time offset. ANSI SQL equivalent to GETDATE

 

SELECT CURRENT_TIMESTAMP

 

Output: 2017-05-16 22:41:23.237

 

Getdate():

returns the current system date and time.

Syntax:

SELECT GETDATE() AS CURRENT_DATE_TIME

 

Output: 2017-05-16 22:43:38.420

 

Sysdatetime:

 

returns a date and time datetime(7) of db. It returns more fractional seconds precisions

 

select Sysdatetime()

 

Output: 2017-05-16 22:52:19.7178978

 

Sysdatetimeoffset()


returns the database system timestamp with database time zone offset

 

select Sysdatetimeoffset()

 

Output: 2017-05-16 22:53:12.6469252 +05:30

 

Sysutcdatetime()

returns system datetime without zone offset as datetime2. It returns more fractional seconds precisions.

 

select Sysutcdatetime()

 

Output: 2017-05-16 17:28:13.8181512

 

Sysdatetimeoffset()


returns system datetime with zone offset UTC and more fractional seconds precisions.

 

select Sysdatetimeoffset()

 

Output : 2017-05-16 22:58:48.2061181 +05:30

 

ISDate(varchar expression)

The isdate function determines whether the passed expression is a valid date or not?

It takes a character expression and returns the 1 if it’s a valid date and 0 if not as shown below:

 

SELECT ISDATE(GETDATE())

 

Output: 1

 

 

SELECT ISDATE(‘SAILLESH’)

 

Output: 0

 

ISDate function returns 0 for datetime2 or datetimeoffset as shown below:

 

select ISDATE(‘2017-05-16 22:33:33.6570000’)

 

Output: 0


select ISDATE(‘2017-05-16 22:33:33.6570000 +00:00’)

 

Output: 0

 

Day() returns the day number of the date of the specified date.

SELECT DAY(GETDATE())

Output: 17

 

 

DECLARE @DATE DATETIME

SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)

SELECT DAY(@DATE)

 

Output: 16

 

Month() function returns the month of the specified date. It takes a datetime expression as a parameter and returns an integer value corresponding to the month.

SELECT MONTH(GETDATE())

Output: 5

 

DECLARE @DATE DATETIME
SET @DATE=CONVERT(DATETIME,’2017-05-16 22:33:33.657′,120)
SELECT MONTH(@DATE)

Output: 5

 

Year() function returns the year from the specified datetime passed in the expression.

SELECT YEAR(GETDATE())

 

Output: 2017

 

 

Datename() returns a string from the datetime expression based on the datepart from the datetime expression. Ex datepart can be a date, year, month.

 

Returning day from the datetime using DateName

 

SELECT DATENAME(DAY,GETDATE())

 

Output: 17

 

Retrieving weekday from the DateName method:

 

SELECT DATENAME(WEEKDAY,GETDATE())

 

Output: Wednesday

 

Retrieving name of the month from the DateName method:

SELECT DATENAME(MONTH,GETDATE())

 

Output: May

 

DatePart table

 

datepart

Abbreviations

year

yyyyyy

quarter

qqq

month

mmm

dayofyear

dyy

day

ddd

week

wkww

weekday

dw

hour

hh

minute

mi, n

second

sss

millisecond

ms

microsecond

mcs

nanosecond

ns

TZoffset

tz

ISO_WEEK

isowkisoww

 

So we can use the datepart shortcut as well in place of full datepart as shown below:

 

Retrieving year from the datetime using DateName:

 


SELECT DATENAME(yy,GETDATE())

 

Output: 2017

 

 

 

DatePart() methods returns the integer representing the specified DatePart type of the passed date.

select datepart(month,getdate())

Output: 5

 

select datepart(year,getdate())

Output: 2017

 

 

DateAdd() function increment specified value to the specified DatePart to the dateTime value as shown below:

 

select Dateadd(MONTH,1,GETDATE())

 

Output: 2017-06-30 22:02:55.437

 

select Dateadd(YEAR,1,GETDATE())

Output: 2018-05-31 22:12:07.897

 

Passing negative value in parameter will result in subtraction or decreasing the DatePart.

 

select getdate();

select Dateadd(YEAR,-5,GETDATE());

 

 

 

Output:


 

 

DateDiff() function returns difference between two datetime.

 

select Datediff(month,’1-1-2017′,GETDATE())

 

Output: 4

 

 

 


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.