将日期格式从 yyyy

本文介绍了将日期格式从 yyyy-mm-dd 更改为 mm/dd/yyyy 派生列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个 .txt 平面文件源.我将其导入 ssis 以进行一些验证、操作以生成 .csv 文件.

I have a .txt flat file source. I am importing that into ssis to do some validation, manipulation to generate a .csv file.

对于某些日期列，我需要将 yyyy-mm-dd 中的日期格式转换为 mm/dd/yyyy.例如:DOB 列有日期 1984-03-16 需要转换为 03/16/1984.我正在使用派生列转换，但无法弄清楚.

I am in need to convert the date format which is in yyyy-mm-dd to mm/dd/yyyy for certain date column. For example: DOB column has date 1984-03-16 needs to be converted to 03/16/1984. I am using derived column transformation but not able to figure it out.

任何帮助都是appriciated！

Any help is appriciated!

推荐答案

假设您的日期列名为 DateCol

Say your Date Column is called DateCol

您需要从该列创建三个派生列，而不是像这样简单地将它们连接起来

You will need to create three derived columns from that column and than simply concatenate them columns something like this

derivedColumns Expression year SUBSTRING(DateCol, 1, 4) day RIGHT(DateCol, 2) Month SUBSTRING(DateCol, 6, 2) NewDate SUBSTRING(DateCol, 6, 2) + "/" + RIGHT(DateCol, 2)+ "/"+SUBSTRING(DateCol, 1, 4)

您的 NewDate 列将采用您想要的格式

Your NewDate Column will be in your desired format