INSERT smalldatetime problem

Hallo,

I am trying to insert date in a table in my database, where column type is smalldatetime. Query works fine if date format ismm.dd.yy:

INSERT INTO DateTable (DateValue) VALUES ('8.18.2007 22:00:00') works fine!

But if the time format is dd.mm.yy it does not work:

INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') does not work!

The error message is:The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Is there any chanceto execute(dd.mm.yy)INSERT INTO DateTable (DateValue) VALUES ('18.8.2007 22:00:00') properly?

Thanx!
Marko

just do like this.....wat ever it may be the input type......

INSERT INTO DateTable (DateValue) VALUES datetime.parse('18.8.2007 22:00:00').tostring("yyyy-MM-dd");

Ramesh

|||

Actually, my query looks something like this:

INSERT INTO Table1(ID, Name, Date) VALUES(100, 'Tom', '18.8.2007')

So, I am not sure where to put datetime.parse and tostring(... Tried to type query(datetime.parse and tostring()...) in SQL Server Management Studio, but I got lot of error messages...

|||

I solved my probleme here:http://forums.asp.net/p/1148508/1866912.aspx

Thanx anyway!
Marko

|||

This would be a better approach (Done in VB.NET, but you should be able to convert it to C# easily). Area's marked with ... are missing, and are irrelevant:

dim conn as new sqlconnection(...)

dim cmd as new sqlcommand("INSERT INTO MyTable(col1) VALUES (@.val1)",conn)

cmd.Parameters.Add("@.val1",sqldbtype.datetime).Value=calCalendar1.selecteddate

conn.open

cmd.executenonquery

conn.close

The above shows how we can supply a datetime to T-SQL as a true datetime instead of first converting a datetime to a string (Which may be affected by either the webserver's current culture or the clients current culture), and then trying to parse the resulting string on SQL Server using it's current culture. By avoiding the datetime->string->datetime conversion process, culture becomes irrelevant.

|||

I will keep this in my record, and will use it for testing my application after it is finished!

Thanx!
Marko