How to convert datetime stamp (1899-12-30T00:00:00.000+0000) from the oracle db response into dd-mm-yyyy format in vba

0
votes

In vba,I am trying to convert datetime stamp(1899-12-30T00:00:00.000+0000) from oracle server into dd-mm-yyyy. I need to have a date format which can be recognised by excel and display it in a specific manner using cell formatting (like excel is parsing the date from sql server into dd-mm-yyyy type on its own using cell formatting but unable to recognise the oracle format for the same).

    For j = 1 To colCount
        mainWorkBook.Sheets("sheet1").Range(colToLetter(j) & 1).Value = node(0).ChildNodes(j - 1).tagName
    Next j
 For i = 0 To rowCount - 1
        For j = 1 To colCount
        If IsDate(node(i).ChildNodes(j - 1).Text) Then
            mainWorkBook.Sheets("sheet1").Range(colToLetter(j) & i + 2).Value = CDate(node(i).ChildNodes(j - 1).Text)
            Else
            mainWorkBook.Sheets("sheet1").Range(colToLetter(j) & i + 2).Value = node(i).ChildNodes(j - 1).Text

            End If
        Next j
    Next i
2
excelvbaoracledatetime
Please include the code you are using to solve this in your question and explain the problems you are having with it. – braX
Allow me a hint as you got several (upvoted) answers to prior questions so far: feel free to upvote fine answers and consider to mark helpful answers (including elder ones) as accepted (i.e. by ticking the hollow green checkmark underneath the voting buttons) - c.f. "Someone answers" - @HemantKumar :-; – T.M.
You could try the Like Operator for a fixed pattern, e.g. in a negative form via If Not If Not TestItem Like "##-##-##T##:##:##.###+####" Then … - c.f. Help at VBA Language Reference - Like Operator - @HemantKumar – T.M.

2 Answers

0
votes

Date values are handled differently in vba, excel and oracle.

Do you really need pre 1900 dates?

Value ranges:

vba: 1 January 100, to 31 December 9999

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers
see https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/date-data-type

oracle: year -4712 to 9999 (excluding year 0)

different datatypes available depending on your needs:
DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE
see https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#SQLRF00204

Excel: 01.01.1900 - 31.12.9999

converting 0 to a date => 00.01.1900
converting 1 to a date => 01.01.1900
...
converting 2958465 to a date => 31.12.9999

I you only want to display the contents of the oracle database you can easily just display the textual content. If you want to calculate with the dates you might want to add a shift to do it in excel or you could do the calculation in vba and only display the results as strings.

If you need "historical" dates in your solution and want to calculate with it search for the different available options: "excel date pre 1900"

see also http://www.creativedeletion.com/2016/04/29/programmers-know-dates.html
see also https://en.wikipedia.org/wiki/Time_formatting_and_storage_bugs

0
votes

If I understand correctly you simply want to convert a string to a valid date; the DateSerial functions offers a reliable way: 

Function timeStampOracle2VBA(myDate) As Date
    Dim Tokens                                    ' i.e. As Variant to allow split
    tokens = Split(Split(myDate, "T")(0), "-", 3) ' limit split results to 3 tokens
    Dim dat
    dat = DateSerial(Val(tokens(0)), Val(tokens(1)), Val(tokens(2)))  ' arguments year, month, date
    timeStampOracle2VBA = dat                     ' return function value
End Function

Example call

Sub test()
   Debug.Print Format(timeStampOracle2VBA("1999-12-30T00:00:00.000+0000"), "dd-mm-yyyy")
End Sub