ISDATE
Use ISDATE function to check if a date time value is valid.
Example:
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
DATEPART function returns an integer value.
Query
-- To get day number of the year
SELECT DATEPART(dy,'2008-09-20 01:26:29.999')
--or
SELECT DATEPART(y,'2008-09-20 01:26:29.999')
Result
264
DATENAME
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.
1. Query
SELECT DATENAME (mm ,'2008-09-23 09:12:44.310' )
Result
September
2. Query
SELECT DATENAME (dw ,'2008-09-23 09:12:44.310' )
Result
Tuesday
DATALENGTH
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.
1. Query
SELECT DATALENGTH('2008-09-23 09:12:44.310')Result
23
2. Query
SELECT DATALENGTH(cast('2008-09-23 09:12:44.310' AS DATETIME))Result
8
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
above table.
DATEADD
To add days or dateparts in a day we can DATEADD function.
Query
SELECT DATEADD(wk,2,'2008-09-23 09:12:44.310')
<span class=Bold>Result</span>
2008-10-07 09:12:44.310
DATEDIFF
We can use DATEDIFF function to get the difference between
two specified dates (with time).
1. Query
SELECT DATEDIFF(day, '2008-09-23 09:12:44.310', '2008-09-26 19:12:44.310')
Result
3
2. Query
SELECT DATEDIFF(day, '2008-09-23 09:12:44.310', '2008-09-26 01:12:44.310')
Result
3
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.
Query
SELECT DATEDIFF(hh, '2008-09-23 09:12:44.310', '2008-09-26 19:10:44.310')
Result
82
