Change the way SQL Server inteprets the Date

Date Problems in SQL Server

Many a times, we face the problem of passing the dates from Code to SQL in the right format.
The problem could be because the controls that we use to generate the dates or we build it that way, don't give us the dates in the required format.

Suppose we want the date to be in mm/dd/yyyy format, the control could be generating it as dd/mm/yyyy.
This, along with many other situations bother us to change the date format before sending it to the SQL statement.

However, there is a solution for this.

By default, the SQL server’s date format is mdy.
You could change the way SQL Server interprets the date , using the SET DATEFORMAT statement.

For e.g. If you want to pass the date as dd/mm/yyyy format, you could prefix and suffix your sql statements with the SET DATEFORMAT statements like this,
            SET DATEFORMAT dmy
                Declare @d DateTime
                Set @d = '25/11/2005'
            SET DATEFORMAT mdy
 
Ensure that  you set it back to the default (mdy) at the end.
 
The default DateFormat can be seen through the Database console command '
DBCC USEROPTIONS'




Note: This is only an alternative. You could well use the CONVERT function in this place setting the date format there.
By [)ia6l0 iii   Popularity  (1157 Views)