SolvedPrivate

Better solution to CASE WHEN MBODDT = 0 THEN 0 ELSE DAYS(CURDATE()) - DAYS(DATE(SUBSTR(MBODDT,1,4)||'-'||SUBSTR(MBODDT,5,2)||'-'||SUBSTR(MBODDT,7,2))) END

Posted on 2015-02-07
89 Views
Hi,

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

CASE WHEN MBODDT = 0 THEN 0 ELSE DAYS(CURDATE()) - DAYS(DATE(SUBSTR(MBODDT,1,4)||'-'||SUBSTR(MBODDT,5,2)||'-'||SUBSTR(MBODDT,7,2))) END

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

Andrew
0
• 3
• 2

LVL 34

Accepted Solution

Gary Patterson earned 500 total points
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
``````

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:

http://www.experts-exchange.com/Database/DB2/Q_28167603.html

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.

http://fiveminutelessons.com/learn-microsoft-excel/convert-text-value-date-excel

- Gary Patterson
0

Author Closing Comment

Hi Gary,

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.

Andrew
0

LVL 34

Expert Comment

Andrew,

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.

https://support.microsoft.com/kb/302084

- Gary Patterson
0

Author Comment

Sorry Gary,

I'm not exactly sure what the terminology is.

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)

DB.Open ("Provider=IBMDA400;Data Source=AUS01;Force Translate=0;Connect Timeout=3")

With cmd
Set .ActiveConnection = DB
.CommandText = .CommandText & sSQL
End With
Set getMovexData = RS
End Function

The SQL statement I provide is something like the following:

SELECT  DAYS(DATE(SUBSTR(MTRGDT,1,4)||'-'||SUBSTR(MTRGDT,5,2)||'-'||SUBSTR(MTRGDT,7,2)))
FROM  MVXBDTA.MITTRA
WHERE MTCONO = 370
AND MTITNO = 'S610'
AND MTWHLO = '900'

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

CASE WHEN MTRGDT = 0 THEN 0  ELSE DAYS(DATE(SUBSTR(MTRGDT,1,4)||'-'||SUBSTR(MTRGDT,5,2)||'-'||SUBSTR(MTRGDT,7,2))) - DAYS('1/1/1900') + 2 END AS MTRGDT

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?

Andrew
0

LVL 34

Expert Comment

No problem.  Forget SQL Server - has nothing to do with this.

Provider=IBMDA400

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.

http://www-01.ibm.com/support/knowledgecenter/?lang=en

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

V5R3:  http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_53/welcome.html
V5R2: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/

Hope that helps.

- Gary
0

Expert Comment

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
0

Featured Post

The code described here does no longer work. Please see replacement Article: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3887-Getting-your-EE-Ranking-statistics-in-Excel-The-Next-Generation.html (http…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…