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

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