Impossible date insertion?

[AndWy]

I am trying to insert a date into sql Server 2000.
I am using the INSERT...VALUES in an SQL statement with VB, But I keep getting the message that SQL cannot convert char to smalldate.
This is hacking me off!!

This is an example of code that does not work -
INSERT INTO someTable(cName, cDate)
VALUES('anyName', CONVERT(DATETIME, ' 2001 - 07 - 26 00:00:00', 102))

Is there another way?

 

[rajeevnandanmishra]

Hi AndWy,
You are facing the problem because of the format
yyyy-mm-dd.
There are many ways to get rid of this problem, But i feel the best way is
SET DATEFORMAT YMD --Issue this command so that sql will use yyyy/mm/dd format for the particular user session.
INSERT ... VALUES ("'"+myDateValue+"'&quot --Just make sure that you are having myDateValue in yyyy/mm/dd format.

If you still face problem, just let me know.