Protecting Your Application from Variations in Date/Time Format

Microsoft's SQL Server development team charts the recommended clauses that help you avoid errors in date/time format.

1 Min Read
ITPro Today logo

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).

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like