?
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
6
Medium Priority
?
123 Views
Last Modified: 2016-10-03
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
Comment
Question by:adb11a
  • 3
  • 2
6 Comments
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 2000 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:

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

by:adb11a
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.

Andrew
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 40596120
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:adb11a
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:

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 36

Expert Comment

by:Gary Patterson
ID: 40596407
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

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
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Being an active EE Expert means to get a lot of (E)EMail, as you certainly know. If you are using Outlook, I'll show you how to minimize your inbox contents without losing anything – even improve the experience by changing the Subject line to facili…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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