Use ISDATE function to check if a date time value is valid.
SELECT ISDATE('1753-1-1 23:00:00.000')
SELECT ISDATE('1752-12-31 23:00:00.000')
By using the above mentioned (in the table) datepart options
as the parameter of the various SQL Server DATETIME based function, we can get
different useful results to be used in our applications. I have described a few
of the important functions with examples here.
DATEPART function returns an integer value.
-- To get day number of the year
SELECT DATEPART(dy,'2008-09-20 01:26:29.999')
SELECT DATEPART(y,'2008-09-20 01:26:29.999')
DATENAME function has similar functionality as DATEPART, but
it has one difference in that it returns an output in string format. We can
understand it better with the following queries.
SELECT DATENAME (mm ,'2008-09-23 09:12:44.310' )
SELECT DATENAME (dw ,'2008-09-23 09:12:44.310' )
System function DATALENGTH returns the size in bytes of an
expression. Though it is not specific to DATETIME only, I have included it to
mention the important fact about dealing with DATETIME in SQL Server. If you
try to execute the following queries, you will see the result as given below.
SELECT DATALENGTH('2008-09-23 09:12:44.310')
SELECT DATALENGTH(cast('2008-09-23 09:12:44.310' AS DATETIME))
Hence, it clearly means that dealing a DATETIME value with
VARCHAR type variables will lead to consuming more memory space. So as far as
possible, we should try to use DATETIME datatype and variables for using and
manipulating date and times.
You can use DATEADD function to get the DATETIME after
adding/subtracting dateparts options like years, months, days, hours, minutes,
seconds, milliseconds, etc. You can use the datepart options listed in the
To add days or dateparts in a day we can DATEADD function.
SELECT DATEADD(wk,2,'2008-09-23 09:12:44.310')
We can use DATEDIFF function to get the difference between
two specified dates (with time).
SELECT DATEDIFF(day, '2008-09-23 09:12:44.310', '2008-09-26 19:12:44.310')
SELECT DATEDIFF(day, '2008-09-23 09:12:44.310', '2008-09-26 01:12:44.310')
If you notice, the actual difference of datetime between the
date/time mentioned in the above first query is 3 days and 10 hours. The result
is 3 (days). In the second query the actual difference is less than 3 days,
still the result is 3 (days). This is because SQL server only checks between
the datepart based on the datepart option we mention in the function and
ignores other parts of the date while calculating.
So while using DATEDIFF we must use proper datepart to get the
correct result. For example, to get actual difference in hours of part of the
dates, we can use "hh" in the datepart option of the query. Check the
above table for datepart option list.
SELECT DATEDIFF(hh, '2008-09-23 09:12:44.310', '2008-09-26 19:10:44.310')