data type DateTime

Hi all!!
I'm developing a project using VS 2005 (C# code) and SQL Server 2005.
I have some problems with the data type 'DateTime'.

In some parts of my project, I have used DataSets and DataAdapters and I can insert in a dataRow a data type 'DateTime' and it is inserted as dd/mm/yyyy hh:mm:ss. That's OK.

But in other parts I'm not using Datatables and DataAdapters and I insert new rows in the database using ExecuteNonQuery, but the data type 'DateTime' in the format dd/mm/yyyy hh:mm:ss is not valid. It only allows formats like mm/dd/yyyy hh:mm:ss, but I want the other format.

Why is dd/mm/yyyy hh:mm:ss working with DataAdapters? Why is dd/mm/yyyy hh:mm:ss not working with ExecuteNonQuery? Is there any way to specify in the Database that I want the format dd/mm/yyyy hh:mm:ss??

Thanks in advance folks,
Javier.

Try to add a setting for DATEFORMAT just before you insert statement, for example:

SET DATEFORMAT dmy; insert into ...

|||Thanks Iori_Jay,
But I've checked that it is necessary to write "SET DATEFORMAT dmy" every time I want to insert a new row in the table that has DateTime attribute. Is there another way of keeping this value within the DB's life??

thanks again|||Since the DATEFORMAT option is a session(means connection to SQL) option, you have to set it in each connection. And within the connection, the option is effective.