How to convert Date Time format from 'YYYY-MM-DD' to 'MM/DD/YYYY HH:MM:SS AM'

Question

I have a .csv file source. I am importing that into ssis to do some changes manipulation to generate a .csv file of special format. Date is coming from .Csv file.

I am in need to convert the date format which is in yyyy-mm-dd to 'MM/DD/YYYY HH:MM:SS AM' for certain date column. For example: Report entry column has date 2017-03-27 needs to be converted to 03/27/2017 HH:MM:SS AM. How do i do it?

Kannan Kandasamy · Answer 1 · 2017-03-28T18:20:16.853

2

You can use FORMAT as below

    select format(getdate(), 'dd/MM/yyyy hh:mm:ss tt')
Select convert(datetime,'your date',103)

edited Mar 28 '17 at 18:20

answered Mar 28 '17 at 18:08

Kannan Kandasamy

13,405
3
25
38

Date is coming from the .csv file and I've to change the format in the package, where the output should look like MM/DD/YYYY HH:MM:SS AM. – toby Mar 28 '17 at 18:17
Then you can use convert with option 103 – Kannan Kandasamy Mar 28 '17 at 18:18

score 2 · Answer 2 · answered Mar 28 '17 at 18:18

2

Use a script component. Assuming the date column is a string:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.date = Convert.ToDateTime(Row.date).ToString("MM/dd/yyyy hh:mm:ss");
}

answered Mar 28 '17 at 18:18

Mark Wojciechowicz

4,287
1
17
25

I tried it. There is a error.The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully – toby Mar 28 '17 at 19:16
Open the script and build it, look at the errors window for what the errors are. You may have to include the column in the script editor, update the column name, do different handling based on the data type. – Mark Wojciechowicz Mar 28 '17 at 19:46

score 1 · Answer 3 · edited May 23 '17 at 12:25

If the Date column is a string you can use the DateTime.ParseExact method in a script component. (assuming that outDate is the output column and inDate is the input column)

using System;
using System.Globalization;


CultureInfo provider = CultureInfo.InvariantCulture;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.outDate = DateTime.ParseExact(Row.inDate,"yyyy-MM-dd",provider).ToString("MM/dd/yyyy HH:mm:ss");
}

for more info on this method you can refer to this links:

Also take a look a my answer in the following link, it is very helpful:

SSIS Source Format Implicit Conversion for Datetime

How to convert Date Time format from 'YYYY-MM-DD' to 'MM/DD/YYYY HH:MM:SS AM'

3 Answers3