String to Datetime(dd/mm/yyyy)

How to derived string to datetime (dd/mm/yyyy)

Input Date :

Start Date 

15/06/2015

30/06/2015

NULL

2015/06/24

Some date are in format dd/mm/yyyy I want to keep them as it is , but I want to convert the NULL one and 2015/06/24 to 24/06/2015.

How can I do this .

July 15th, 2015 3:47am
To go from a varchar date: 'dd/mm/yy' to a mm/dd/yy datetime in SSIS, you could use something like the following as the expression in a derived column transformation:

ISNULL(CHAR_DATE) ? NULL(DT_DATE) : (dt_date)(SUBSTRING(CHAR_DATE,4,2) + "/" + SUBSTRING(CHAR_DATE,1,2) + "/" + SUBSTRING(CHAR_DATE,7,2))
Free Windows Admin Tool Kit Click here and download it now
July 15th, 2015 6:03am

its not working

I used this expression in derived column:

ISNULL([Start Date]) ? NULL(DT_DATE) : (DT_DATE)([Start Date])

It worked when there is no null or space or blank

but it failed when the data is like this :

Please suggest.

July 15th, 2015 7:50am

Hi kalsubu,

After testing the issue in my environment, we can refer to the following expression to achieve your requirement:
RIGHT((DT_WSTR,10)(DT_DBDATE)(TRIM([Start Date]) == "" ? NULL(DT_WSTR,10) : TRIM([Start Date])),2)+"/"+SUBSTRING((DT_WSTR,10)(DT_DBDATE)(TRIM([Start Date]) == "" ? NULL(DT_WSTR,10) : TRIM([Start Date])),FINDSTRING((DT_WSTR,10)(DT_DBDATE)(TRIM([Start Date]) == "" ? NULL(DT_WSTR,10) : TRIM([Start Date])),"-",1)+1,2)+"/"+LEFT((DT_WSTR,10)(DT_DBDATE)(TRIM([Start Date]) == "" ? NULL(DT_WSTR,10) : TRIM([Start Date])),4)

The following screenshot is for your reference:

Thanks,
Katherine Xiong

Free Windows Admin Tool Kit Click here and download it now
July 16th, 2015 2:12am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics