Posted on 2015-02-07
Last Modified: 2016-10-03

I'm using a pass thru query in Excel to convert a date MBODDT (20150206) to 42041.  I use the following statement:


Surely there is an easier statement than having to deconstruct MBODDT.

I've looked everywhere with no luck.

I've found SELECT DAYS(CURRENT DATE)-DAYS(DATE('1900-01-01')) AND TRIED TO SUBSTITUTE MBODDT for CURRENT DATE bit it wants MBODDT to be in the format 'YYYY-MM-DD'.

Question by:adb11a
  • 3
  • 2
LVL 35

Accepted Solution

Gary Patterson earned 500 total points
ID: 40595911
Well, if the dates were properly stored in DB2 as DATE columns, they would come into Excel properly with no transformation.  But it looks like they are character columns in YYYYMMDD format from the query above.  That means you're going to have to do something in either DB2 or in Excel to get them into a usable date.

Sometimes it is nice to deal with difficult date formats like this using views on the server side, since you can do it once and anyone can then use it.  I've even written programs to automatically generate views for all your tables containing difficult dates - particularly nice for end users accessing the database using tools like Excel, since the difficult transformations are already done for them.  

For example, you could CREATE VIEW over the underlying table to add a column formatted as a proper date:

create view mylib/myview AS SELECT column1, column2 ... MBODDT, date(left(MBODDT,4)||'-'||substr(MBODDT,5,2)||'-'||right(MBODDT,2)) as MBODDT2 FROM mytable

Open in new window

Then just use the MBODDT2 column by querying the view.  DB2 DATE fields imported into Excel through most mechanisms will automatically be formatted into Excel "serial number" dates.                                    

Another easy alternative is to create a UDF to handle these char-to-date conversion.  Then you can just use the UDF in your queries.  A little more complex for end users, but pretty simple for developers:

Finally, of course, you can also deal with this on the Excel side using DATEVALUE + TEXT functions or a similar technique.  This can be useful when dealing with databases where you can't create views or UDFs.

- Gary Patterson

Author Closing Comment

ID: 40596007
Hi Gary,

Thanks for you reply.

Currently we only use our server to read from tables, insert and delete records.  I've never UDFs in SQL Server but after looking into the from the link you've provided I will be when I get back to work Monday.  I am at home and don't have access to the SQL Server apart from a connection to it.  

I didn't want to use the Excel solution as it takes time to insert a column, apply the formula, paste it as a value and delete the original column.  Having the date supplied in the correct format is preferred.

LVL 35

Expert Comment

by:Gary Patterson
ID: 40596120

I thought this was an IBM i DB2 table we were talking about.  I gave you DB2 syntax for the view and UDF.  Your question was tagged with DB2.  Perhaps this is a DB2 linked table from SQL Server?  If so, you'd create the view or UDF on the AS/400 DB2 server, not on SQL server.

I'm not sure how you are going about this, but when working with Excel, I sometimes create VBA programs or use Excel Automation from .NET programs to handle all the Excel-side work - especially if I'm i an environment where I can't create server-side objects.

- Gary Patterson
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.


Author Comment

ID: 40596179
Sorry Gary,

I'm not exactly sure what the terminology is.

And thinking about your response I'm not sure either.

I'm a VBA developer so I'm ok with Excel automation and all that.  I have an Excel form with listboxes that allow users to SELECT fields and apply criteria.

From their selection I build an SQL statement and then obtain the data from AS400 using the following:

Public Function getAS400Data(sSQL)
    Dim DB As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim RS As ADODB.Recordset
    Set DB = New ADODB.Connection
    DB.CursorLocation = adUseClient
    DB.Open ("Provider=IBMDA400;Data Source=AUS01;Force Translate=0;Connect Timeout=3")

    Set RS = New ADODB.Recordset
    Set cmd = New ADODB.Command
    With cmd
        Set .ActiveConnection = DB
        .CommandType = adCmdText
        .CommandText = .CommandText & sSQL
    End With
    RS.Open cmd, , adOpenDynamic, adLockReadOnly
    Set getMovexData = RS
End Function

The SQL statement I provide is something like the following:

  AND MTITNO = 'S610'
  AND MTWHLO = '900'

Currently the user selects the field (MTRGDT) and through VBA I change it to :


so I can have it return an integer representing today (42043 for Sun 8th Feb, 2015 from MTRGDT=20150208).

My problem has been the user is able to select many fields and filters making the SQL statement quite large.  I was hoping there was a function available that could convert a field of YYYYMMDD to the integer I seek. I don't want to do it in Excel due to the time it takes take.

There are many different fieldnames across many table names. A function supplying the fieldname is ideal.

In your reply you mention creating a view.  I thought you were talking about SQL Server but I was mistaken.  How do I do that in AS400?

LVL 35

Expert Comment

by:Gary Patterson
ID: 40596407
No problem.  Forget SQL Server - has nothing to do with this.


That's the AS?400 / iSeries IBM i (we're just going to use the name IBM i from here on)  OLE DB Provider - used to allow Microsoft client applications to connect to IBM i DB2.

For your purposes, a UDF is a nice solution and easy to implement - just one object to create in DB2.  You'd have to create a view for each table containing dates.

You create a view or a UDF in IBM i DB2 pretty much just like you do in SQL Server, MySQL, Oracle, etc.  You connect to the database and issue DDL commands CREATE VIEW or CREATE FUNCTION.  IBM i DB2 has a green-screen SQL interface command called STRSQL.  IBM also provides a Windows-based tool called System i Navigator (part of IBM i Access for Windows that installed the IBMDA400 OLE DB Driver from).   Drill down your start menu into IBM i Access for Windows and look for System i Navigator.  Run SQL Scripts function is down in the Database area of Navigator.

There is also a web interface which may or may not be running on your system.  HTTP://yoursystem:2005 to see.  Navigate to Database to get to Run SQL Scripts.

You can use other tools - IBM Data Studio, Squirrel, MS Query, pretty much anything that allows you to establish a connection using ODBC/OLE DB/JDBC/.NET provider and run SQL statements.

I don't know what OS release you're running on your IBM i, but documentation for recent versions is in the IBM Knowledge Center.

And documentation for a number of older versions is available online:


Hope that helps.

- Gary

Expert Comment

by:Art Sumitra
ID: 41827224
If the date string values all represent a date since the first day of year 1000, then the following expressions using implicit cast from the numeric data type into a TIMESTAMP character string, will by implicit cast for a VARCHAR(14), allow for what is in my opinion, a simpler expression than effectively concatenating the substrung components of the date to generate one of the more familiar implicitly recognized CHAR(10) DATE formats. The first is the casting the YYYYMMDD numeric value into a TIMESTAMP value cast to a DATE. The second shows that same expression referenced in the DAYS scalar, although the use of DATE in the second expression is optional, due again, to implicit cast.  The third shows that shorter form, with the DATE scalar removed, with the DAYS scalar implicitly recognizing the 14-character argument as a TIMESTAMP.

    date( mboddt concat '000000' ) as mboddt_date          

    days( date( mboddt concat '000000' ) ) as mboddt_days

    days( mboddt concat '000000' ) as mboddt_days

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The code described here does no longer work. Please see replacement Article: (http…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question