Dealing with DB2 “Decimal Dates” in Microsoft-Land

In the system I’m working with the dates in DB2 are defined as type “DECIMAL”, the they are stored as offsets from 19000000.
Note, it’s not the number of days since 1900-00-00, but literally the the current date (YYYYMMDD less 19000000).

T-SQL converting numeric DB2 date to something more readable

Thus, when using a T-SQL query to the Linked-Server, the date can be converted as follows:

SELECT RDABDT + 19000000 as RDABDT2,  * from Linked.x.dbo.table order by RDABDT desc 

RDABDT2 shows as 20200219. No need to convert when you sort, as the larger decimals are already the more recent dates.

C# – Converting to DB2 Date (integer or decimal)

        public static int EDIDateToDBDateInteger(string CCYYMMDD)
            int rt = 0;
            if (CCYYMMDD == null)
                throw new ApplicationException("EDIDatetoDBDateInteger CCYYMMDD argument passed is null");
            if (CCYYMMDD.Length != 8)
                throw new ApplicationException(
                   "EDIDatetoDBDateInteger CCYYMMDD length is not 8, value received=" + CCYYMMDD);
            rt = Convert.ToInt32(CCYYMMDD) - 19000000;
            return rt;

        public int XMLDateToDB2DateInteger(string convertDate)
            string sMonth = DateTime.Parse(convertDate).ToString("MM");
            string sDay = DateTime.Parse(convertDate).ToString("dd");
            string sYear = DateTime.Parse(convertDate).Year.ToString();
            string sdateWorker = sYear + sMonth + sDay;
            int iDateWorker = Int32.Parse(sdateWorker);
            iDateWorker = iDateWorker - 19000000;
            return iDateWorker;

        public int AnyDateToDB2DateInteger(string dateToConvert)
            string yearStr = DateTime.Parse(dateToConvert).ToString("yyyy");
            string monthStr = DateTime.Parse(dateToConvert).ToString("MM");
            string dayStr = DateTime.Parse(dateToConvert).ToString("dd");
            int db2Date = Convert.ToInt32(yearStr + monthStr + dayStr) - 19000000;
            return db2Date;


Leave a Reply

Recent Posts




All Rights Reserved Theme by 404 THEME.