4

I am using the code below to build a sql input statement, which extracts data row by row from an excel sheet. 

v_sql_input_string += "'" & Trim(Replace(Convert.ToString(.Rows(i).Item(3)), "'", "''")) & "', "

One of my columns is formatted as "Custom" MM/dd/yyyy format in excel. Date displays as 01/22/2018 in excel as well. When being extracted into this string, it reformats as the date INT. Example 43102.

I'm somewhat new to VB, so forgive me for my ignorance. What am I doing wrong here?

Hadi
  • 36,233
  • 13
  • 65
  • 124
CRudiger
  • 79
  • 1
  • 8

1 Answers1

3

Microsoft excel stores date and time as decimal (it is called Excel Serial)

You can use DateTime.FromOADate() function to convert it to date.

You can use the following code:

If Double.TryParse(.Rows(i).Item(3), New Double) Then

    v_sql_input_string += "'" & Trim(Replace(DateTime.FromOADate(CDbl(.Rows(i).Item(3))).ToString("MM/dd/yyyy"), "'", "''")) & "', "

Else

    v_sql_input_string += "'" & Trim(Replace(.Rows(i).Item(3).ToString(), "'", "''")) & "', "

End If

For additional information you can refer to this link:

Hadi
  • 36,233
  • 13
  • 65
  • 124