I'm querying an AS400 database and the dates are formatted as CYYMMDD. I've learned that I can convert this to a date format recognized by Excel using this, DATE((MYTABLE.MYFIELD + 19000000) || '000000') But I cannot make any sense of how this is working. I would like to understand the logic behind this. Can anyone interpret what this is doing and share? Thanks

If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

SQL Date CYYMMDD Conversion

Thread starter cr731
Start date Apr 12, 2017

cr731

Well-known Member

Apr 12, 2017

I'm querying an AS400 database and the dates are formatted as CYYMMDD. I've learned that I can convert this to a date format recognized by Excel using this,

Code:

DATE((MYTABLE.MYFIELD + 19000000) || '000000')

But I cannot make any sense of how this is working. I would like to understand the logic behind this. Can anyone interpret what this is doing and share?

Thanks