Date format in MS SQL

Hi

I am using VB6.0 and MS SQL 7.0. I got a table with a date field(type datetime). When I tried to enter date using sql stmt from VB it gave error. Same was the case when I tried from Query Analyzer. But when I typed the minute, seconds etc with the field it accepted. It then looks like this.

insert tmpgrn (GRNNo,CID,CAddress,GRNDate,Remarks,BOENo,BOEDate) values(11,1,'qwer','18-10-1999 00:00:00.000',1,23,'18-10-1999 00:00:00.000')

This date is picked from the date picker control and so I dont want to store time info. What should I do inorder for SQL Server to accept 18/10/199 as the data in a datetime field or how will the sql statement look like

Thanks in advance

James

[czimmerman]

Try converting the string value to a date via the CDate function.

Charles Zimmerman
http://www.freevbcode.com

[kieran_rush]

Actually I had the same error with SQL, here are the reasons I found and the solutions I gave to this

1.- No date format, try sendig your date string with the SQL command CAST for example
Q = "INSERT INTO XXXX VALUES (" & CAST(txtdate) AS DATETIME & ")
Conn.Execute Q

2.- If you are using multiple languages (as I do), the format for English is mm/dd/yyyy, the format for Spanish is dd/mm/yyyy, so here you have to check the configuration of the server and the clients, to have all the same language, or in the VB code you have to do a lot of API calls to detect the language of the client

3.- If you send your string without quotations you will recieve an error, always the date string must be between quotations
Hope this helps you, if you need further help ask for it
Regards
RSV

1. Add a data environment to the project.
2. Add a connection to the data environment.
3. Add a command to this connection.
4. Edit the property of the command to build your sql command.
5. While in the build environment, enter "=10/29/99" in the criteria field after you already selected the field to include in the query.
6. Look at tge sql command that is created for you automatically.
7. It will say something like WHERE myDate = ToDate('10/29/99', 'mm/dd/yy').