DATEFORMAT and ISDATE Command in SQL

DATEFORMAT

DATEFORMAT is a SQL command & only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format. Also it has no effect on the actual storage of the table.

Syntax:

SET DATEFORMAT defined_format

Example:

SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '03/07/2015 09:42:01.12';
SELECT @datevar;
GO

Result:

2015-07-03 09:42:01.1200000

ISDATE

ISDATE is a SQL command used to check out that given string as date or time or datetime is in valid format like "mdy, dmy, ymd, ydm, myd, and dym" or not. However, the "ydm" format is not supported for date. ISDATE returns 1 if given string is in correct format & returns 0 if given string is not in a defined format.

Syntax:

ISDATE('string in date formate')

Example:

SELECT ISDATE('03/07/2015')

Result:

1