SQL Server and DateTime fields

Question

Hi,

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' as the field format.

Thanks!

Answer 1

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

Answer 2

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) )

Answer 3

Try this to set the format of field using str_to_date fucntion

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