gpt4 book ai didi

ado.net - 如何在 SSIS 表达式生成器中使用日期时间值来制定 SQL 命令?

转载 作者:行者123 更新时间:2023-12-02 13:39:48 33 4
gpt4 key购买 nike

我正在尝试使用 Ado.Net Sql 命令从带有日期过滤器的表中选择数据。

SELECT  COLUMN1
, COLUMN2
FROM TABLENAME
WHERE DATE_INSERTED > @[User::LastInsertDate]

Date_Inserted@[User::LastInsertedDate] 都是 DateTime 类型。当我尝试在表达式生成器中计算表达式时,出现以下错误;

The expression might contain an invalid token, an incomplete token, oran invalid elemnt, it might not be well-formed, or might be missingpart of a required element such as a parenthesis.

根据已接受答案的原始修订进行的问题诊断:

Here is my understanding of your question. I believe that you createdtwo variables under package scope. A variable named LastInsertDateof DateTime data type and another variable named SqlQuery ofString data type to store the SQL SELECT command.

Variables declaration

You set the EvaluateAsExpression property on variable SqlQuery toTrue. You then entered the following command SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE DATE_INSERTED > @[User::LastInsertDate]

Expression Builder

When you clicked EvaluateAsExpression, you got the following errormessage:

Expression cannot be evaluated. Additional information: Attempt to
parse the expression "SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE
DATE_INSERTED > @[User:LastInsertDate]" failed. The expression might
contain an invalid token, an incomplete token, or an invalid element.
It might not be well-formed, or might be missing part of a required
element such as a parenthesis.

Error message

最佳答案

这里的问题是您尝试存储在变量 SqlQuery 中的值未用双引号括起来。 动态查询的字符串值应括在双引号内。

将文本括在双引号中时,不能使用日期时间变量 LastInsertDate就这样。您需要将日期时间变量转换为字符串,但如果您只是将日期时间值转换为字符串,则可能会出现意外的格式。为了安全起见,我建议使用 DATEPART将日期时间值转换为格式 YYYY-MM-DD hh:mi:ss 的字符串的函数。这是执行此操作的完整表达式。

"SELECT COLUMN1, COLUMN2 FROM TABLENAME WHERE DATE_INSERTED > '" +
(DT_STR, 4, 1252) DATEPART("yyyy", @[User::LastInsertDate])
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm", @[User::LastInsertDate]), 2)
+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd", @[User::LastInsertDate]), 2)
+ " " + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hh", @[User::LastInsertDate]), 2)
+ ":" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mi", @[User::LastInsertDate]), 2)
+ ":" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("ss", @[User::LastInsertDate]), 2)

当您单击“评估表达式”时,您将在“评估值”部分中看到带有日期时间值的字符串。

希望有帮助。

Corrected

关于ado.net - 如何在 SSIS 表达式生成器中使用日期时间值来制定 SQL 命令?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12798289/

33 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com