如何使用 SSIS 表达式将格式为 yyyyMMdd 的字符串转换为日期? [英] How to convert string in format yyyyMMdd to date using SSIS expression?

问题描述

我在 csv 中有一个日期 20130131 我正在尝试将其转换为 2013-13-01> 在 SSIS 中使用派生列.我用过这个表达式，但它似乎不起作用.

I have a date 20130131 in csv which I'm trying to convert to 2013-13-01 using Derived Column in SSIS. I have used this expression but it does not seem to work.

(DT_DBTIMESTAMP)(SUBSTRING(DATE_DECISION_TO_REFER,1,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,3,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,5,2))

(DT_DBTIMESTAMP)(SUBSTRING(DATE_DECISION_TO_REFER,1,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,3,2) + "-" + SUBSTRING(DATE_DECISION_TO_REFER,5,2))

如何重写此表达式以生成具有数据类型 DT_DBTIMESTAMP 的值的正确输出?

How do I rewrite this expression to produce the correct output with a value of data type DT_DBTIMESTAMP?

推荐答案

根据 Kyle Hale 的建议，下面是我的回答

As per Kyle Hale suggestion, below is my answer

    SUBSTRING([DATE_DECISION_TO_REFER],1,4) + "-" +
 SUBSTRING([DATE_DECISION_TO_REFER],5,2) + "-" + SUBSTRING([DATE_DECISION_TO_REFER],7,2)

教育你，让你永远不会再遇到这个问题，这就是表达的工作方式

To educate you so that you will never face this issue again, this is how expression works

 Get 4 characters from DATE_DECISION_TO_REFER starting at position 1, add a dash,
 get 2 characters from DATE_DECISION_TO_REFER starting at position 5,
 add a dash then add 2 characters from DATE_DECISION_TO_REFER starting at position 7.

希望这会有所帮助.

这篇关于如何使用 SSIS 表达式将格式为 yyyyMMdd 的字符串转换为日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！