gpt4 book ai didi

datetime - 在 SSIS 中将日期转换为当前月份名称的字符串?

转载 作者:行者123 更新时间:2023-12-02 22:12:50 27 4
gpt4 key购买 nike

我收到错误,

Error converting data type varchar to date.

来自这个表达式

(DT_STR,30,1252)@[User::ToDate]

DO NET Source [40]: An error occurred executing the provided SQL command: "EXEC [dbo.StoredProcedure] @ToDate = '01-maj-2018'

Error: 0xC004706B at Import (See Expression!), SSIS.Pipeline: "ADO NET Source" failed validation and returned validation status "VS_ISBROKEN".

@ToDate = '01-maj-2018'

如何更改它,使我的 @ToDate 变为 01-may-2018 而不是 01-maj-2018

我尝试搜索,有很多类似的问题,但没有一个关于此特定错误的问题。为什么它甚至让我的字符串有 maj

最佳答案

我同意@AlexanderVolok,看起来该软件包的 LocalID 与英语不同,或者在操作系统区域设置中选择的日期时间文化信息不同。

(1) 使用条件 ? 更改月份名称:

如果您可以更改这些属性,那么它可能会解决问题,否则您可以在数据流任务之前添加一个表达式任务,该任务使用条件运算符将日期转换为不同格式的字符串? :,例如(假设您正在处理波兰月份名称):

@[User::NewDataString] = 
LEFT((DT_WSTR,50)@[User::ToDate],2) + "-" +
(SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "maj" ? "may" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "stycz" ? "Jan" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "lut" ? "feb" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "mar" ? "mar" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "maj" ? "may" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "czerw" ? "jun" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "lip" ? "jul" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "sierp" ? "aug" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "wrzes" ? "sep" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "pazdzier" ? "oct" :
SUBSTRING((DT_WSTR,50)@[User::ToDate], FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) + 1, FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",2) - FINDSTRING((DT_WSTR,50)@[User::ToDate],"-",1) - 1) == "listopad" ? "nov" : "dec" )
+ "-" + RIGHT((DT_WSTR,50)@[User::ToDate],4)

然后您应该将新变量作为参数传递。

(2) 将数据格式更改为 yyyy-MM-dd

您还可以使用表达式任务,将月份转换为数值,例如:

@[User::NewDateString] = 
RIGHT((DT_WSTR,50)@[User::ToDate],4) + "-" +
RIGHT("0" + (DT_WSTR,50)DATEPART("mm", @[User::ToDate]),2) + "-" +
LEFT((DT_WSTR,50)@[User::ToDate],2)

引用文献

关于datetime - 在 SSIS 中将日期转换为当前月份名称的字符串?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/54781887/

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