Date Function : DATEFROMPARTS() and TIMEFROMPARTS()

DATEFROMPARTS()

It returns the date value on specified year, month and day value.

It available in SQL Server 2012.

Syntax:

DATEFROMPARTS ( year, month, day )

Example:

SELECT DATEFROMPARTS(2010,10,30) AS DATEPARTS

OutPut:

DATEPARTS

2010-10-30

TIMEFROMPARTS()

It returns the full time on specified hour, minute and second.

It available in SQL Server 2012

Syntax:

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Example:

SELECT TIMEFROMPARTS(10,23,45,123,3) AS TIMEPARTS

OutPut:

TIMEPARTS

10:23:45.123

SELECT TIMEFROMPARTS(10,23,45,123,4) AS TIMEPARTS

OutPut:

TIMEPARTS

10:23:45.0123

Date Function : DATEDIFF()

This is used to find the difference between two dates based on DatePart type

The DATEDIFF() function returns the time between two dates.

Syntax:

DATEDIFF(DatePart,StartDate,EndDate)

DatePart is Abbreviation used same as in DatePart() function.

StartDate and EndDate are valid date time.

Example:

SELECT DATEDIFF(day,'2015-01-23 10:00:44.470','2015-10-23 10:00:44.470') AS DiffDate

--The Difference between millisecond, nanosecond and microsecond should be less otherwise it will throw below error.

Msg 535, Level 16, State 0, Line 1

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

SELECT DATEDIFF(millisecond,'2015-10-23 10:00:43.470','2015-10-23 10:00:44.470') AS DiffDate

OutPut:

DatePart	Type	Return Value
year	yy, yyyy	1
quarter	qq, q	7
month	mm, m	21
dayofyear	dy, y	638
day	dd, d	638
week	wk, ww	91
weekday	dw, w	638
hour	hh	15312
minute	mi, n	918720
second	ss, s	55123200
millisecond	ms	100
microsecond	mcs	1000000
nanosecond	ns	1000000000