2. Home
  3. Articles
  4. Microsoft
  5. SSIS
  6. SSIS Convert a string into a date

SSIS Convert a string into a date

SQL Server Integration Services
Hits: 37719
What?
Please note: the following article is not a solution but a page of various methods and date conversions which I try and use depending on the situation.

This is a quick article on how I got time conversion working in an SSIS package which read from a text file. Note that the example below converts a string in European Date Format (ie. "ddmmyyyy" to "dd/mm/yyyy"). Also, my data flow imports two dates one which was imported as a string (DT_WSTR) and another as a integer (DT_R8).

Why?
It took me a long time to figure this and it was only by trawling through columns that someone mentioned that maybe all the data in that column is not consistent? This made sense as I need to parse the string to extrapolate the date, month and year.

How?
I used two "Derived Column" tasks for this after giving up with a "Data Conversion" task. I then parse the date, month and year out using SUBSTRING:
copyraw 
-- Assuming [This_Date]="21/03/2012"
(DT_DATE)(This_Date)


-- Assuming [This_Date]="20120321" (YYYYMMDD)
(DT_DATE)(SUBSTRING(This_Date,6,2) + "/" + SUBSTRING(This_Date,9,2) + "/" + SUBSTRING(This_Date,1,4))
 

-- Assuming [This_Date]="21032012" (DDMMYYYY)
(TRIM(This_Date)=="")? (DT_DBTIMESTAMP)"1901-01-01 00:00:00" : (DT_DBTIMESTAMP)(SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00")


-- Explicit: Assuming [StartDate]="21032012" (DDMMYYYY)
(DT_DATE)(SUBSTRING((DT_STR,8,1252)StartDate,1,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,3,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,5,4))
  1.  -- Assuming [This_Date]="21/03/2012" 
  2.  (DT_DATE)(This_Date) 
  3.   
  4.   
  5.  -- Assuming [This_Date]="20120321" (YYYYMMDD) 
  6.  (DT_DATE)(SUBSTRING(This_Date,6,2) + "/" + SUBSTRING(This_Date,9,2) + "/" + SUBSTRING(This_Date,1,4)) 
  7.   
  8.   
  9.  -- Assuming [This_Date]="21032012" (DDMMYYYY) 
  10.  (TRIM(This_Date)=="")? (DT_DBTIMESTAMP)"1901-01-01 00:00:00" : (DT_DBTIMESTAMP)(SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00") 
  11.   
  12.   
  13.  -- Explicit: Assuming [StartDate]="21032012" (DDMMYYYY) 
  14.  (DT_DATE)(SUBSTRING((DT_STR,8,1252)StartDate,1,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,3,2) + "/" + SUBSTRING((DT_STR,8,1252)StartDate,5,4)) 
Error: Description: "Invalid character value for cast specification". Exceptions for text file where I needed two derived column tasks, the first checks if the Date was blank and puts a NULL string. The second takes this string and converts it to a date:
copyraw 
-- Assuming EndDate is optional (can be null) and in format DDMMYYYY:

-- Derived column task 1: Replace 'Date'
TRIM(Date) != "" ? TRIM(Date) : "00000000"

-- Derived column task 2: Add as new Column 'DC_Date' 
-- (note this is a separate task in the data flow to the previous one):
(ISNULL(Date) || (TRIM(Date) == "00000000")) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")
  1.  -- Assuming EndDate is optional (can be null) and in format DDMMYYYY: 
  2.   
  3.  -- Derived column task 1: Replace 'Date' 
  4.  TRIM(Date) != "" ? TRIM(Date) : "00000000" 
  5.   
  6.  -- Derived column task 2: Add as new Column 'DC_Date' 
  7.  -- (note this is a separate task in the data flow to the previous one): 
  8.  (ISNULL(Date) || (TRIM(Date) == "00000000")) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00") 


So although your SSIS IDE (Visual Studio?) will give you a useless Microsoft message saying something like:
copyraw 
Error: Data conversion failed while converting column "This_Date" (3833) to column "This_Date" (3932).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Extract dates from string dates" (3614)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DC_This_Date" (3833)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
  1.  Error: Data conversion failed while converting column "This_Date" (3833) to column "This_Date" (3932).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". 
  2.   
  3.  Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Extract dates from string dates" (3614)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DC_This_Date" (3833)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. 
All this blab actually means is that there is a chance one of your date values is BLANK, NULL or contains a non-numerical value in a number such as COMMA (,) and therefore the parsing (to extract year for example) will fail and stop the task (based on default settings).

Additional
copyraw 
-- Take a "dd/mm/yyyy" string and convert to date (when (DT_DATE)ThisDate by itself doesn't work)
(DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4))
  1.  -- Take a "dd/mm/yyyy" string and convert to date (when (DT_DATE)ThisDate by itself doesn't work) 
  2.  (DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4)) 

Known Issues
copyraw 
[Convert Date [4228]] Error: An error occurred while attempting to perform a type cast.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Convert Date" (4228) failed with error code 0xC0209029 while processing input "Derived Column Input" (4229). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

-- often when processing a datasource, the function is being applied to a NULL date timestamp.
  1.  [Convert Date [4228]] Error: An error occurred while attempting to perform a type cast. 
  2.   
  3.  [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Convert Date" (4228) failed with error code 0xC0209029 while processing input "Derived Column Input" (4229). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure. 
  4.   
  5.  -- often when processing a datasource, the function is being applied to a NULL date timestamp. 

Dealing with Date NULLs:
Fix: Put conditional statements to account for NULLs.
copyraw 
-- Standard
TRIM(This_Date) == "" ? NULL(DT_DATE) : (DT_DATE)This_Date

-- if data source column is of datatype string and european date format: return SQL date
(TRIM(This_Date) == "") ? (DT_DATE)"1900-01-01" : (DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4))

-- if data source column is of datatype string and european date format: return SQL datetime
(TRIM(This_Date) == "") ? (DT_DBTIMESTAMP)"1900-01-01" : (DT_DBTIMESTAMP)( SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00.000")

-- if nulls
ISNULL(This_Number) ? 0 : This_Number
ISNULL(This_String) ? "" : This_String
ISNULL(This_Date) ? NULL(DT_DATE) : (DT_DATE)This_Date
ISNULL(This_Date) ? "" : (DT_STR)This_Date

-- if blanks
LTRIM(This_String)=="" ? "" : LTRIM(This_String)
LTRIM(This_Date)=="" ? (DT_DATE)"1900-01-01" : (DT_DATE)This_Date

-- last column when datasource is flat file (account for line break):
ISNULL(Last_Column) ? "" : TRIM(Last_Column)
  1.  -- Standard 
  2.  TRIM(This_Date) == "" ? NULL(DT_DATE) : (DT_DATE)This_Date 
  3.   
  4.  -- if data source column is of datatype string and european date format: return SQL date 
  5.  (TRIM(This_Date) == "") ? (DT_DATE)"1900-01-01" : (DT_DATE)(SUBSTRING(This_Date,1,2) + "/" + SUBSTRING(This_Date,4,2) + "/" + SUBSTRING(This_Date,7,4)) 
  6.   
  7.  -- if data source column is of datatype string and european date format: return SQL datetime 
  8.  (TRIM(This_Date) == "") ? (DT_DBTIMESTAMP)"1900-01-01" : (DT_DBTIMESTAMP)( SUBSTRING(This_Date,7,4) + "-" + SUBSTRING(This_Date,4,2) + "-" + SUBSTRING(This_Date,1,2) + " 00:00:00.000") 
  9.   
  10.  -- if nulls 
  11.  ISNULL(This_Number) ? 0 : This_Number 
  12.  ISNULL(This_String) ? "" : This_String 
  13.  ISNULL(This_Date) ? NULL(DT_DATE) : (DT_DATE)This_Date 
  14.  ISNULL(This_Date) ? "" : (DT_STR)This_Date 
  15.   
  16.  -- if blanks 
  17.  LTRIM(This_String)=="" ? "" : LTRIM(This_String) 
  18.  LTRIM(This_Date)=="" ? (DT_DATE)"1900-01-01" : (DT_DATE)This_Date 
  19.   
  20.  -- last column when datasource is flat file (account for line break): 
  21.  ISNULL(Last_Column) ? "" : TRIM(Last_Column) 

Last Troubleshooting Check:
The problems here relate to when our datasource was a flat file connection. We had to change the Format under General to Ragged Right (I had been setting it to "fixed width" but then got problems with it not understanding the last two characters on the row (CR & LF).


Fixes of interest:
copyraw 
-- assuming This_Decimal="00000.00" or "123456.78"  (Do NOT use "Replace" and instead "Add as new column")
(DT_DECIMAL,2)TRIM(This_Decimal)


-- assuming This_Numeric="0000000.0000" or "1234567.8901"
LTRIM(This_Numeric) == "" ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(This_Numeric)


-- further?
ISNULL(ContributionValue) || (LTRIM(ContributionValue)=="") ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(ContributionValue)


-- it's a number
(DT_NUMERIC,12,4)TRIM(ContributionValue)


-- a thousandth separator in the data (eg. 2,534.0000)?
ISNULL(ContributionValue) || (LTRIM(ContributionValue) == "") ? "0.0000" : TRIM(REPLACE(ContributionValue,",",""))
  1.  -- assuming This_Decimal="00000.00" or "123456.78"  (Do NOT use "Replace" and instead "Add as new column") 
  2.  (DT_DECIMAL,2)TRIM(This_Decimal) 
  3.   
  4.   
  5.  -- assuming This_Numeric="0000000.0000" or "1234567.8901" 
  6.  LTRIM(This_Numeric) == "" ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(This_Numeric) 
  7.   
  8.   
  9.  -- further? 
  10.  ISNULL(ContributionValue) || (LTRIM(ContributionValue)=="") ? (DT_NUMERIC,12,4)0 : (DT_NUMERIC,12,4)TRIM(ContributionValue) 
  11.   
  12.   
  13.  -- it's a number 
  14.  (DT_NUMERIC,12,4)TRIM(ContributionValue) 
  15.   
  16.   
  17.  -- a thousandth separator in the data (eg. 2,534.0000)? 
  18.  ISNULL(ContributionValue) || (LTRIM(ContributionValue) == "") ? "0.0000" : TRIM(REPLACE(ContributionValue,",","")) 


My Most Frequent Fix:
The data files I have to work with are rarely consistent. On some rows the date column will have a string of 8 characters long in the format DDMMYYYY and on other rows, this is blank with spaces instead, I still get the Error: The conditional operation failed.
copyraw 
-- I've been given data as a Flat File with fixed columns (Ragged Right).  
-- "Date" is the last column and can be blank in the source.
-- "Date" must default to current date if blank for the target database (cannot be NULL).


-- PROBLEM:
-- assuming date could be blank (has spaces) or in format DDMMYYYY
(TRIM(Date) == "") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")

-- Yields:
-- [MyDerivedColumnTask1 [4228]] Error: The conditional operation failed.


-- SOLUTION:

-- a) create a first derived columns task that checks if the column is blank and defaults a value (Replace current column if you like)
(TRIM(Date)=="") ? "00000000" : TRIM(Date)

-- b) then a second derived columns task after which checks if the value is "00000000"
(TRIM(Date) == "00000000") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00")
  1.  -- I've been given data as a Flat File with fixed columns (Ragged Right). 
  2.  -- "Date" is the last column and can be blank in the source. 
  3.  -- "Date" must default to current date if blank for the target database (cannot be null). 
  4.   
  5.   
  6.  -- PROBLEM: 
  7.  -- assuming date could be blank (has spaces) or in format DDMMYYYY 
  8.  (TRIM(Date) == "") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00") 
  9.   
  10.  -- Yields: 
  11.  -- [MyDerivedColumnTask1 [4228]] Error: The conditional operation failed. 
  12.   
  13.   
  14.  -- SOLUTION: 
  15.   
  16.  -- a) create a first derived columns task that checks if the column is blank and defaults a value (Replace current column if you like) 
  17.  (TRIM(Date)=="") ? "00000000" : TRIM(Date) 
  18.   
  19.  -- b) then a second derived columns task after which checks if the value is "00000000" 
  20.  (TRIM(Date) == "00000000") ? (DT_DBTIMESTAMP)GETDATE() : (DT_DBTIMESTAMP)(SUBSTRING(Date,5,4) + "-" + SUBSTRING(Date,3,2) + "-" + SUBSTRING(Date,1,2) + " 00:00:00") 


Date back into String
copyraw 
-- Converting yyyy-mm-dd hh:ii:ss to ddmmyyyy
RIGHT("00" + (DT_STR,2,1252)DATEPART("dd",DateVerified), 2) 
+ RIGHT("00" + (DT_STR,2,1252)DATEPART("mm",DateVerified), 2) 
+ (DT_STR,4,1252)DATEPART("yyyy",DateVerified)
  1.  -- Converting yyyy-mm-dd hh:ii:ss to ddmmyyyy 
  2.  RIGHT("00" + (DT_STR,2,1252)DATEPART("dd",DateVerified), 2) 
  3.  + RIGHT("00" + (DT_STR,2,1252)DATEPART("mm",DateVerified), 2) 
  4.  + (DT_STR,4,1252)DATEPART("yyyy",DateVerified) 
Category: SQL Server Integration Services :: Article: 493

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.