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

Who is Participating?
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
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
adb11aAuthor Commented:
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.

Gary PattersonVP Technology / Senior Consultant Commented:

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

adb11aAuthor Commented:
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?

Gary PattersonVP Technology / Senior Consultant Commented:
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
Art SumitraCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.