asked
Converting DT_I4 into DT_DBDATE
Hi All, Need some advise and help. I have a table which has date columns defined as Int, as part of transformation process I need to populate fields into staging and do some data manipulation on them. The issue that I'm facing now is the metadata for the source column is Int(Datefield), when I get it through DB connector the datatype is assigned as DT_I4, I ausing a derived column and trying to convert this into Datetime using (DT_DBDate)[Col_Name]. There are no blank spaces or Null data in the source, for sure. I even tried converting the datatype to string and then to date (DT_DBDate)(DT_STR,20,1252)[Col_Name]. But still I get an error at persorfimg a type cast. I can do this transformation using SQL but still want to find as to why is this happening. The expression is > TRIM((DT_STR,20,1252)[Col_Name]) == "" || ISNULL([Col_Name])? NULL(DT_DBDATE): (DT_DBDATE)[Col_Name] another expression which i tried is > TRIM((DT_STR,20,1252)[Col_Name]) == "" || ISNULL([Col_Name])? NULL(DT_DBDATE): (DT_DBDATE)(DT_STR,20,1252)[Col_Name]. Please Help....
I was able to solve my issue, apparently I had misquoted the position of starting and ending points on one of the concatenated substring. However, I'm wondering as to why cannot a DT_I4 be converted to DT_DBDate, when MSDN cast table clearly states that the conversion is possible.
Remember, if an answer to your question is helpful, vote it up by clicking on the thumbs up symbol next to it. If the answer solves your problem, be sure to click the check box next it to identify it as the solution. Do this for all your questions.
Is the data actually being stored as an int in the underlying table? Or just being picked up as an int in the data source's metadata? In the first case, you might be able to fix the problem by doing some maths in a derived column (depending on what the integer actually represents). In the latter case, you may be able to resolve it by using the advanced editor on the data source and changing the metadata there
tomgough79, Date in the source table is stored as Int, the reason I'm constructing this into string was because initially I was under the impression that DT_I4 cannot be converted to DT_DBDATE, but on looking up the MSDN cast page, the table clearly states that other than DT_I8 or DT_UI8, int values can be converted to DT_DBDate. When you say Math, what would that correspond to, meaning what calculation do you suggest.
It was a matter really of what that integer represents - days I'd guess, so you could use the dateadd function to add that many days to whatever the base date is (1900-01-01?) and end up with a date that way
You cannot convert integer data type directly to date type in SSIS. First you need to build a string and than convert it into an integer (unless you will use a `Script Component` and write a `C#` or `
VB.NET` code for the conversion. When converting from a string to `DT_DBDATE`, then the string has to be in format `yyyy-mm-dd`. So in general there are two approaches.. Construct the required string by mathematical operations: `(DT_DBDATE)((DT_WSTR,4)( [Col_Name] / 10000) + ''-'' + (DT_WSTR,2)( ([Col_Name] - [Col_Name]/10000*10000) / 100 ) + ''-'' + (DT_WSTR,2)( [Col_Name] - [Col_Name] / 100 * 100))` Or by by substrings from string representing the integer: `(DT_DBDATE)(LEFT((DT_WSTR,8) [Col_Name],4) + ''-'' + SUBSTRING( (DT_WSTR,8)[Col_Name] ,5, 2) + ''-'' + RIGHT((DT_WSTR,8)[Col_Name],2))` Of course, if you would like NULL Handling, add the condition for NULL. You can also see MSDN: [Cast (SSIS Expression)][1] [1]:
http://msdn.microsoft.com/en-us/library/ms141704.aspx
Pavel, I had also earlier tried casting it with substring and concatenating the data with '-' to make it yyyy-mm-dd format , later converting it into DT_DBDate, some of rows went through and some threw an error, on error output data was as in the metadata format as the ones which were cast. No Alpha Numeric in them of any sort.
13 People are following this question.
