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.