Date format independant of local settings

Hello,
how can we make the Date format for SQLServer7.0 independant of locale settings. We have a Web Server and a database server running multiple projects having different requirement for date formats viz mm/dd/yyyy and dd/mm/yyyy.

I suppose SQLServer takes the database local date format as the current date format. The problem can be solved if i can indicate in my SQL queries the format being used in the query. This also eliminates any accidental change in database server format (from mm/dd/yyyy to dd/mm/yyyy), which would mean 1st Feb being inserted into the database as 2nd Jan.

Any Ideas !!!

Regards,

AshutoshHave you looked at SET DATEFORMAT

SET DATEFORMAT mdy
GO
DECLARE @.datevar datetime
SET @.datevar = '12/31/98'
SELECT @.datevar
GO

SET DATEFORMAT ydm
GO
DECLARE @.datevar datetime
SET @.datevar = '98/31/12'
SELECT @.datevar
GO

SET DATEFORMAT ymd
GO
DECLARE @.datevar datetime
SET @.datevar = '98/12/31'
SELECT @.datevar
GO|||Hello,
thanks the problem seems to be solved by using the SET DATEFORMAT command. While ADODB connection object we have to execute the above command as a action query -
Connection.Execute "SET DATEFORMAT dmy"

The connection thus follows this new date format.

Thanks Again :-)

Regards,

Ashutosh|||This is only a problem with character date formats.
If you always transfer dates with format yyyymmdd then you should never have a problem.

dd mmm yyyy also works as long as you don't use other languages.

Date and Language settings - SQL Server 2005

Despite the documentatin stating that SQL Server will pick up and use the locale settings, why will the database only accept entry in american (mdy) format?

MDY is the default datetime format in SQL Server.

You can change the format using "SET DATEFORMAT",

see help here: http://msdn2.microsoft.com/en-us/library/ms189491.aspx

Xinwei

|||Thanks,
I tried that and it didn't appear to do anything. I've got the ldefault language settings as British and all the examples were coming out in dmy displayed format despite changing the format.
The documentation (SQL Books) states that it should use the server locale.
I solved the problem using code to transform the date from a 'DateTime picker' into an acceptable format.
It was just an expression of frustration about the silly bugs in Microsoft products, especially the 2005 range (how about taking a comment as part of an enumeration, - VB 2005; not letting you move directly to row 0 in a BindingSource.DataSource, you have to MoveLast then MoveFirst - C# 2005; the ComboBox control spewing garbage out until it settles down, thus messing up any text or value changes; the setting the sorted property on a bound combobox sets the value returned to the position in the combobox list instead of the bound data value; The weird error messages often having nothing to do with the type and position of the error..... These are some of the recent value added features.