SQL Server and DateTime fields

I've got this strange problem which I'm sure is well known - When I insert a date like '20/08/2010' I mean it to be as 'dd/mm/yyyy' where MSSQL expects it to be 'dd/mm/yyyy'.

How can it be changed for MSSQL to expect 'dd/mm/yyyy' a开发者_运维百科s the field format.

Thanks!

Take a look at Setting a standard DateFormat for SQL Server to see a couple of ways. SET DATEFORMAT MDY is one way

See http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx for the (not-so-intuitive) set of date format codes.

(Sounds like you want to CONVERT(DATETIME, '20/08/2010', 103) )

Try this to set the format of field using str_to_date function

insert into values (str_to_date('08/09/2010', '%d/%m/%Y'))