I have an input column "MonthName" which has values in the following string format 22-MAY-2017 02:29:33.00. I would like to convert this into Datetime data type in the destination table.
For that the following conversion needs to be done
22-MAY-2017 02:29:33.00 to 22-05-2017 02:29:33.00
How do i achieve this in Derived Column task.
I am using below expression to fetch the month name part of the value but i don't think it servers much of my purpose
SUBSTRING(MonthName,FINDSTRING(MonthName,"-",1) + 1,FINDSTRING(MonthName,"-",2) - FINDSTRING(MonthName,"-",1) - 1) <br/>
the above expression creates a new column with all the month names ex: may, june, july.
Using Derived Column Transformation
You can use the following expression
LEFT([MonthName],3) +
(SUBSTRING( [MonthName],4,3) == "JAN" ? "01" :
SUBSTRING( [MonthName],4,3) == "FEB" ? "02" :
SUBSTRING( [MonthName],4,3) == "MAR" ? "03" :
SUBSTRING( [MonthName],4,3) == "APR" ? "04" :
SUBSTRING( [MonthName],4,3) == "MAY" ? "05" :
SUBSTRING( [MonthName],4,3) == "JUN" ? "06" :
SUBSTRING( [MonthName],4,3) == "JUL" ? "07" :
SUBSTRING( [MonthName],4,3) == "AUG" ? "08" :
SUBSTRING( [MonthName],4,3) == "SEP" ? "09" :
SUBSTRING( [MonthName],4,3) == "OCT" ? "10" :
SUBSTRING( [MonthName],4,3) == "NOV" ? "11" :
SUBSTRING( [MonthName],4,3) == "DEC"? "12":"")
+ RIGHT([MonthName],17)
Using Script Component
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,"dd-MMM-yyyy HH:mm:ss.ff",provider).ToString("dd-MM-yyyy HH:mm:ss.ff");
}
for more info on this method you can refer to this links:
- DateTime.ParseExact Method
- C# DateTime.Parse
Also take a look a my answer in the following link, it is very helpful:
- SSIS Source Format Implicit Conversion for Datetime
You can use Sql Server's CONVERT function for the calculated value, no need to mess with SubString and FindString; and then just add add in the calculated value to the table definiton via the ALTER command. The only tricky part was finding which format type to use for your format, and the table of values is on the linked page.
Proof of Concept:
CREATE TABLE Prototype (
PrototypeID INT IDENTITY(1,1) NOT NULL
, Month_Name VARCHAR(100) NOT NULL
) ON [PRIMARY]
GO
INSERT Prototype (Month_Name)
VALUES ('22-MAY-2017 02:29:33.00')
, ('22-apr-2017 02:29:33.00')
, ('22-Dec-2017 02:29:33.00')
GO
ALTER TABLE Prototype
ADD Month_DateTime AS Convert(DateTime, Month_Name, 107)
GO
SELECT * FROM Prototype
=======================
PrototypeID Month_Name Month_DateTime
1 22-MAY-2017 02:29:33.00 2017-05-22 02:29:33.000
2 22-apr-2017 02:29:33.00 2017-04-22 02:29:33.000
3 22-Dec-2017 02:29:33.00 2017-12-22 02:29:33.000
4 22-October-2017 02:29:33.00 2017-10-22 02:29:33.000
