INF: Using a Stored DATETIME Key for Data Retrieval

• 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:

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

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

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

You can find DKEY.EXE (size: 18782 bytes) 
                     , a self-extracting file, on the following services:

      On the www.microsoft.com home page, click the Support icon.
      Click Knowledge Base, and select SQL Server.
      Enter kbfile <FILENAME>.EXE, and click GO!
      Open the article, and click the button to download the file.

      ftp ftp.microsoft.com
      Change to the Softlib/Mslfiles folder.
      Get <FILENAME>.EXE

      On the Edit menu, click Go To, and then click Other Location.
      Type "mssupport" (without the quotation marks).
      Double-click the MS Software Library icon.
      Find the SQL Server product area.
      Locate and Download <FILENAME>.EXE.