INF: Using a Stored DATETIME Key for Data Retrieval

ID: Q64229

The information in this article applies to:

Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

If you have a table with a key of type SQLDATETIME, after an entry has been selected by an application and you save the key in a program variable of type DBDATETIME, the best way to retrieve the customer information (given that you have the key stored in a variable of type DBDATETIME) is the following:

When you save your key as type DBDATETIME, it is possible to lose a portion of the unique time information. Converting the key to a string of type 9 or 109 prior to selection is the most reliable method of using a key of type DATETIME for further data retrieval.

MORE INFORMATION

There are several things you need to consider when using a stored DATETIME key for data selection:

Uniqueness of the key. Both dbbind() and dbconvert() produce string results that do not contain the second and millisecond fields of the DATETIME data. This could cause serious data redundancy problems if these values were used as primary keys. Fortunately, you can avoid this problem by using the SQL CONVERT() function when selecting the data. Be sure to convert the data using either type 9 or type 109 (as shown in the sample code). This will retain the entire value of the stored data field.

Storing the converted data as a string. Be sure to append a NULL ("/0") on the end of the string in your storage structure. Strncpy() does not do this for you.

Use of a string when selecting on type DATETIME. You must add a set of quotation marks on either side of the selection string for the SQL syntax to be correct.

There is a file in the Software/Data Library named DKEY.EXE that contains a sample program that uses a stored DATETIME key for selection into a table.

The following file is available for download from the Microsoft Software Library:

~ DKEY.EXE

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

Q119591 : How to Obtain Microsoft Support Files from Online Services

Additional query words: dblib DB-Lib Windows NT

Keywords : kbprg SSrvDB_Lib SSrvProg SSrvTrans
Version : 4.2 | 4.2 | 4.2
Platform : MS-DOS OS/2 WINDOWS
Issue type :