SQL Convert Legacy Integer Date to actual date for Comparison issue

480 pts.
Tags:
.NET
AS/400
SQL
I have a PF with a date broken in to three fields: 4 digit year, month, and day.
I'm converting it in my SQL statement as follows:
DATE(year||'-'||month||'-'||day)
This works great for valid dates! The problem is there are invalid dates in the file such as 2/29(non leap year),30,31 or 4/31. There's not much I can do to fix the data.
 Thoughts on how to work around this in SQL and still get a valid result set? I'm consuming it in a .NET application and it's bombing out because I'm trying to convert the dates on the fly in a BETWEEN statement.
Thanks, Dave


Software/Hardware used:
iseries, as400
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

The easiest thing would be to identify the invalid dates and correct them in the database. Is the date data validated on entry or has the data been modified by a DFU, SQL or other means where there is no validation?  The only fix I can see is one nasty statement with a bunch of case statement in it.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TheRealRaven
    How "invalid" are the date components? Are there any zero day-of-month? Invalid month? Strange years? I.e., how rigorously have reviewed errors? Various CASE structures could help, but they could get ugly fast.
    35,660 pointsBadges:
    report
  • carlosdl
    I don't know what you are doing with the dates on the application side, but I would consider not trying to build the dates in SQL, but bringing the original data to the application and having some logic there to create a valid date when the initial conversion fails.
    84,805 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: