I used Visual Basic (VB) 6.0 to develop a program for a database application that runs on SQL Server 7.0. When VB inserts a record into a SQL Server table's date field, the date changes to an incorrect date. For example, in the VB function, the date is 2001-09-27. However, when I insert the record into the SQL Server table, the date becomes 1905-05-20. How do I solve this problem?
The safest way to present dates (and times) to SQL Server and to make sure they survive passage through middleware such as ADO, OLE DB, and ODBC or middle-tier applications without unexpected results (e.g., 2/1 might represent February 2001 or January 2002) is to use a standard format. ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses that Table 1 shows. If you adopt these clauses in your code, you can protect your application from international variations in the date/time format. For more information, see the "International Features of SQL Server 2000" white paper at the Microsoft Web site (http://www.microsoft.com/sql/techinfo/development/2000/intfeatures.asp).