SSIS脚本任务-VB-将日期提取为INT而不是日期字符串 [英] SSIS Script Task - VB - Date is extracting as INT instead of date string

问题描述

我正在使用以下代码构建sql输入语句，该语句从Excel工作表中逐行提取数据.

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)), "'", "''")) & "', "

我的一列在excel中被格式化为自定义" MM/dd/yyyy格式.日期在Excel中也显示为01/22/2018.当提取到该字符串中时，它将重新格式化为日期INT.示例43102.

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.

我对VB有点陌生，所以请原谅我的无知.我在这里做什么错了?

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

推荐答案

Microsoft Excel将日期和时间存储为十进制(称为Excel Serial)

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

您可以使用

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

您可以使用以下代码:

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:

• C#FromOADate和Excel日期

这篇关于SSIS脚本任务-VB-将日期提取为INT而不是日期字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！