November 30, 2009 at 11:20 pm
Hi All,
i have a date coloumn in excel & format is dd/mm/yyyy but data in some rows as dd/m/yyyy
(ex: 12/3/1999 )so when iam trying to pull the data from xle to sql destination it is coming as null.
and if we have space in datecoloumns how to remove them?
Thanks in advance...
December 1, 2009 at 7:48 am
[font="Comic Sans MS"]
Hi,
You can use derived column to transform as required.
2nd problem first ..
Use replace string function to remove the space(s):
REPLACE([DateColumn]," ","")
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 1, 2009 at 8:10 am
[font="Comic Sans MS"]
Regarding first problem - here is a generic code snippet (courtesy Andy Leonard) - you can modify accordingly (or use as a whole) add a Derived Column Transformation . In the Derived Column editor, add a Replace MyDateTime function with the following Expression:
(DT_STR,4,1252)DATEPART("yyyy",(DT_Date)MyDateTime) + "-" + (LEN((DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime)) + "-" + (LEN((DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime)) + " " + (LEN((DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime)) + ":" + (LEN((DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime)) + ":" + (LEN((DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime)) + "." + RIGHT(MyDateTime,LEN(MyDateTime) - FINDSTRING(MyDateTime,".",1))
This expression tests for lengths of month, day, hour, minute, and second that contain only one character (digit, in this case). If it finds only one, it places a "0" in front of it.
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply