SQL Server Date Settings

Question

We have recently moved some data from an SQL Database instance to another one in another location.

I seemed to have noticed that there are some facets of our old database instance where the date is passed as String to the SQL server and SQL server is able to parse it properly. For example, the application would simply pass a string value of "15/01/2010" and the database would immediately recognize it as 5th of January 2010. Is there a setting in SQL server which I need to turn on or modify cause right now, when I passed the same string value, what happens is that an error is being generated cause it cannot understand the string value passed as a date.

Thanks for your inputs.

Answer 1

try

SET DATEFORMAT dmy

but you should only use 'safe' formats when using strings

same formats are YYYYMMDD and yyyy-mm-ddThh:mi:ss.mmm (no spaces). It doesn't matter with these 2 formats what your language settings are

Take a look here: Setting a standard DateFormat for SQL Server

If you need to convert output use cast or convert

select convert(varchar(30),getdate(),101) 

03/08/2010

select convert(varchar(30),getdate(),101) +' ' 
 +  convert(varchar(30),getdate(),108)

03/08/2010 15:21:32