2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Dee
  • 111
  • 2
  • 9
  • That is not a conversion, that is just applying a different format. `DateTime.Parse("22-MAY-2017 02:29:33.00")` will create a DateTime object for you that you should be able to save in your db (assuming you're trying to do this in C#, otherwise please remove that tag). – Rufus L Jun 19 '17 at 15:54
  • @Deepthi why not accepting any answer, your new question asked shows that this issue is solved and you are using a script component. You can read the [Tour page](http://www.stackoverflow.com/tour) to learn more on accepting and upvoting answers – Hadi Jun 21 '17 at 23:25

2 Answers2

2

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:

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

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
Mad Myche
  • 1,075
  • 1
  • 7
  • 15