DATEDIFF function in SQL server ?

To return the count of Specifed Datepart between the startdate and enddate in SQL server,we can use the DATEDIFF function.

SYNTAX

DATEDIFF (DATEPART,STARTDATE,ENDDATE)

You can find the datepart and its abbreviations below.

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

STARTDATE - Refers to start date

ENDDATE - Refers to end date

Its return type is INT.

EXAMPLE

You can refer the examples in the following screenshot.

You can also get the (milli/micro/nano) seconds difference between two dates.

Note : If the return value exceeds the INT maximum value, then the error will be returned.

Error :

Msg 535, Level 16, State 0, Line 3

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.

How to validate the date-time in SQL server ?

TO validate the date-time in SQL server,use the following syntax.

Syntax:

ISDATE(Expression)

Return Type:

INT

Example:

Select ISDATE('2016-04-16 12:20:51.550')

Output :

1

SELECT ISDATE('16/04/2016')

Output:

0

Explanation:

ISDATE function will return 1 if the expression is a valid date,else it will return 0.

How to Get the Current date in SQL server ?

To get the current date and time in SQL server,use the following syntax.

Syntax:

Getdate()

Example:

Select Getdate()

Output:

2016-04-16 12:09:52.613

Now the SQL server installed system data and time will be displayed.