By using SET DATEFORMAT <format> through we can set
SQL Server to accept a different format of date string to be assigned and used
in DATETIME variable.
For example, try to execute the following query:
DECLARE @dt DATETIME
SET @dt = '23/2008/09'
SELECT @dt
In this query we tried to assign a date string of format DYM
(date-year-month) into a DATETIME variable, which SQL Server does not accept by
default. SQL Server may show the following error. The conversion of a char data
type to a datetime data type resulted in an out-of-range datetime value.
To allow SQL Server to do so, we need to execute the
following statement.
Now executing this query again will be successful.
Query
DECLARE @dt DATETIME
SET @dt = '23/2008/09'
SELECT @dt
Result
2008-09-23 00:00:00.000
One important point to note is that SET DATEFORMAT
<format> command remains valid only for the current connection.
