How to convert the date format in the "YYYY-MM-DD" format to "MM / DD / YYYY HH: MM: SS AM"
I have a .csv source. I'm importing this into ssis to do some change manipulation to create a custom formatted CSV file. The date comes from a .Csv file.
I need to convert the date format that is in yyyy-mm-dd to "MM / DD / YYYY HH: MM: SS AM" for a specific date column. For example: the report input column has the date 2017-03-27, which should be converted to 03/27/2017 HH: MM: SS AM. How to do it?
You can use FORMAT as shown below
select format(getdate(), 'dd/MM/yyyy hh:mm:ss tt')
Select convert(datetime,'your date',103)
Use the 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");
}
If the Date column is a string, you can use the method DateTime.ParseExact
in the script component. (assuming 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 information about this method you can refer to these links:
- DateTime.ParseExact Method
- C # DateTime.Parse
Also see my answer in the following link, it's very helpful:
- SSIS Source Code Format Implicit Conversion for Datetime