How to convert a DB2 date-time string into an Excel Date

Question

I'll regularly get an extract from a DB/2 database with dates and timestaps formatted like this:

2002-01-15-00.00.00.000000
2008-01-05-12.36.05.190000
9999-12-31-24.00.00.000000

Is there an easier way to convert this into the Excel date format than decomposing with substrings?

DB2date = DateValue(Left(a, 4) + "/" + Mid(a, 6, 2) + "/" + Mid(a, 9, 2))

thanks for your help!

Answer 1

I'm sure you could cook something up with Regex's if you really cared to. It wouldn't be any 'better' though, probably worse.

If you'll forgive a bit of C# (I havn't touched VB in years, so I don't know the function calls anymore) you could also do:

DB2string = "2002-01-15-00.00.00.000000";
DB2date = DateValue(DB2string.SubString(0, 10).Replace('-', '/'));

But again, you're not really gaining anything. Can you give an example of where your current code would break?

Answer 2

It's not clear if you talk about formula functions or VBA functions.

Formula functions

Don't use the DateValue function, which expects a string; use the Date function, which expects numeric Year, Month, Day:

=DATE(INT(LEFT(A1,4)),INT(MID(A1,6,2)),INT(MID(A1,9,2)))

assuming that the date-as-string is in A1.

VBA functions

Similar calculation as above, just use the DateSerial function instead:

dt= DateSerial(Int(Left$(dt$, 4), Int(Mid$(dt$, 6, 2)), Int(Mid$(dt$, 9, 2)))

Answer 3

in VBA, dateValue() can convert the first part of the string into a date:

? dateValue("2002-01-15")

    15/01/2002

So the right way to get it for you will be

? dateValue(left("2002-01-15-00.00.00.000000",10))

This will always give you the right answer as long as DB2 always give you a "YYYY-MM-DD" date. The format of the result (dd/mm/yy, mm-ddd-yyyy, etc) will depend on the local settings of your computer/specific settings of your cell.

If you want to extract the "time" part of your string, there is this timeValue() function that will eventually make the job.

Answer 4

If you want to include the time information you have more to do; DateValue discards it.
TimeValue can evaluate the time part down to seconds, so you can add them:

= DateValue(Mid(a, 1, 10)) + TimeValue(Mid(a, 12, 8))

But your sample data presents another problem: it has both time values "00.00.00" and "24.00.00".
The second one gags the TimeValue function, so you will need to code for the special case.