how to convert columns which is in datetime to integer
Hi
I have columns in the format as 2001-12-03 00:00:00:00:00 date time data type i want to convert them in to integer such as
20011203 etc.. is that possible to convert what would be the expression for this
help would be appricated
Thank you
Sri.Tummala
March 12th, 2012 1:09pm
Hi Sri.Tummala,
Yes, you must be able to convert such a text to a numeric (e.g. Integer) data type.
You can use a Derived Column Transformation with <Replace Column> using the following SSIS Expression:
(DT_I4)(Replace(REPLACE(@[User::StringVar], "-","")," 00:00:00:00:00",""))
Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2012 2:30pm
Hi,
Assuming that your data is coming from source file like Excel and data type is "DT_WSTR" or "dt_str" then take a derived column and write the expression as:
(dt_i4)(substring([column],1,4)+substring([column],6,2)+substring([column],9,2))
latha
March 12th, 2012 2:31pm
Hi ,
Thanks for your reply i would like to convert date time to integer ie.. the column has values in this way
2001-12-03 00:00:00:00:00 have to be converted some thing like this 20011203
in to integer format thank you
Sri.Tummala
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2012 3:28pm
(DT_I4)(Replace(REPLACE((ST_WSTR,50)Col1, "-","")," 00:00:00:00:00",""))
Arthur My Blog
March 12th, 2012 3:50pm
Hi arthur,
I got this following error
used expression was
(DT_I4)(REPLACE(REPLACE((DT_WSTR,50)the_Date,"-","")," 00:00:00:00:00",""))
[Derived Column [1539]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (1539)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "Derived Column 1" (1587)" 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.
Sri.Tummala
Free Windows Admin Tool Kit Click here and download it now
March 12th, 2012 4:55pm
I tested my expression with value 2001-12-03 00:00:00:00:00 in
Expression Tester.
I have mine as (DT_I4)(REPLACE(REPLACE((DT_WSTR,50)@[User::StringVar],"-","")," 00:00:00:00:00",""))
And I get 20011203 as a result.
I think then the variable's the_Date value is not either the same. Or even you may very well need a patch "Cumulative update package 2 for SQL Server 2008 "
I just happened to know about it, and the link to it is http://support.microsoft.com/kb/959794.
Let me know how it goes and good luck!Arthur My Blog
March 12th, 2012 5:02pm
Hi Artur,
I am using
select CAST(REPLACE(CONVERT(CHAR(10),The_Date,111),'/','')
AS INT) from table (The_Date) is a column
in the SQL command it worked for me i will mark your one as answer
Thanks artur Sri.Tummala
Free Windows Admin Tool Kit Click here and download it now
March 13th, 2012 4:44am