Problem solve Get help with specific problems with your technologies, process and projects.

Converting a DB2 date to SQL Server

Here's how to enter a DB2 date in the format '0001-01-01' as a date in Microsoft SQL Server.

We recently had a problem with SQL Server 7 bombing out when trying to process a view of our DB2 table where some of the date fields were initialized with the value '0001-01-01'. In SQL Server the earliest date recognized is 1753-01-01, so it could not process date fields with this value.

We solved the problem by creating a logical view of the physical table containing every element needed by SQL Server but redefined the date fields as alpha. This returns the date as an 8-char alpha field to SQL Server when used as a view. An IF-ELSE test on the alpha field can be performed by SQL Server and returns a NULL if the leftmost 4 characters of the field equals '0001'. SQL Server date functions can interpret the NULL value without problems.

Here's a sample of the original table DDS source:

*************** Beginning of data*************************************
0001.00      A          R DROPR

0002.00      A            DRACCT         9A         COLHDG('Account' 'Number') 
0003.00      A            DREFRC          L         COLHDG('Election' 'Form')
0004.00      A                                      DATFMT(*ISO)

0005.00      A            DRDTOE          L         COLHDG('Date' 'of' 'Entry')
0006.00      A                                      DATFMT(*ISO)

0007.00      A            DRTERM          L         COLHDG('Termination' 'Date')
0008.00      A                                      DATFMT(*ISO)

0009.00      A            DRMAXD          L         COLHDG('Max' 'Date')

Here's a sample of the logical defined over the physical table.
0000.01      A*************************************************************

0000.02      A* DROP File View                             *

0000.03      A*

0000.04      A* This View for SQL Server allows initialized date fields to be  
0000.05      A* imported and processed by converting date type fields to alpha 
0000.06      A*

0000.07      A*  This contains DROP member records

0000.08      A*                                                           *

0000.09      A*************************************************************

0001.00      A          R DROPR                     PFILE(DROP)

0002.00      A            DRACCT                    COLHDG('Account' 'Number') 
0003.00      A            DREFRC          A         COLHDG('Election' 'Form' 'R')
0004.00      A                                      DATFMT(*ISO)

0005.00      A            DRDTOE          A         COLHDG('Date' 'of' 'Entry')
0006.00      A                                      DATFMT(*ISO)

0007.00      A            DRTERM          A         COLHDG('Termination' 'Date'
0008.00      A                                      DATFMT(*ISO)        
0009.00      A            DRMAXD          A         COLHDG('Max' 'Date')

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free SQL Server tips and scripts.
  • Tip contest: Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical SQL Server questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: SQL Server tips, tutorials, and scripts from around the Web.

Dig Deeper on SQL Server Interoperability

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.