We help IT Professionals succeed at work.

# Converting As400 date formats YYMMDD and CYYMMDD for use in Access

on
Dear Gurus:
I need to convert dates imported from an AS400 server into an Access 2003 database. The dates are both pre-2000 and post 2000 with a Century notation on the post-2000 years.
I'm not a programmer, but I do know how to apply and use Module code.

I would like Module code to handle either case:
991204 needs to become 12/04/1999 and
1021204 needs to convert to 12/04/2002
I've found several examples - but none seem to handle both pre- and post-2000.
Is this possible??

I have a report with 12 different dates that need to be converted and also need to work in calculations, and I am really stuck...
NEC
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Commented:
Try this function (it should be placed in module):
``````Public Function DateAS400(A As String) As Date
Dim D As Integer, M As Integer, Y As Integer, L As Integer
L = Len(A)
Y = 1900
If L < 6 Then
MsgBox "Error in Date value", vbOKOnly
DateAS400 = 0
Exit Function
ElseIf L = 7 Then
Y = 2000
A = Right(A, 6)
End If
Y = Y + CInt(Mid(A, 1, 2))
M = CInt(Mid(A, 3, 2))
D = CInt(Mid(A, 5))
DateAS400 = DateSerial(Y, M, D)
End Function
``````
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Here's a routine that will do the job:
Function CorrectDate(strDateValue As String) As Variant

Dim strDate As String

On Error GoTo CorrectDate_Error

strDate = Format\$(CLng(strDateValue), "0000000")

CorrectDate = DateSerial(1900 + Left\$(strDate, 3), Mid\$(strDate, 4, 2), Mid\$(strDate, 6, 2))

CorrectDate_Exit:
On Error Resume Next

Exit Function

CorrectDate_Error:
MsgBox "Error occured"
CorrectDate = Null
Resume CorrectDate_Exit

End Function

You can call it from a query and it will return a variant with a date value in it.  You can then format that to whatever you need.

Jim.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Or a one-liner:

datDate = CDate(Format(19000000 + YourAS400DateValue, "@@@@/@@/@@"))

/gustav

Commented:
All seem to work - except when the field has no date.  Then the error handling takes over.  I apologize - I forgot to mention that some fields would not contain a date (zero value).
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If so, using CVDate should work:

datDate = CVDate(IIf(IsNull(YourAS400DateValue), Null, Format(19000000 + YourAS400DateValue, "@@@@/@@/@@")))

/gustav
CERTIFIED EXPERT

Commented:
You can call function as:
DateAS400([As400DateField] & "")
If you don't like error message - comment it

Commented:
ALS315 - Thank you for sticking with me!
The call function and commenting-out worked well...just one little problem left - when the date is "zero" the date conversion value is "12:00:00AM".
I changed the format of the query value to be SHORT DATE and the conversion becomes "12/30/1899".
Is there any way to code the module to make these fields blank - or even a zero value is fine.
I can find a work-around, but I'd like to have the conversion handled in the module.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
There is a way.
This expression will return the date for input values representing a date and Null for input values of 0 or Null:

= CVDate(IIf(Nz(YourAS400DateValue, 0) = 0, Null, Format(19000000 + YourAS400DateValue, "@@@@/@@/@@")))

/gustav
CERTIFIED EXPERT

Commented:
You can use Gustav's method everywhere (I prefer isnull function):
IIF(isnull([As400DateField]), Null, DateAS400([As400DateField]))
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
> I prefer isnull function ..

Sorry, I didn't read carefully, I understood some fields could have a zero, but that seems not to be the case. Thus, as the field contains either Null or a date expression, please disregard my previous post.

/gustav

Commented:
I split the points because both experts provided not only the solution - but some good guidence.  I have applications where both solutions can work beautifully while providing much needed improvements to the "inherited" database that I am using.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks for the feedback.

/gustav