Converting of date to integer

PRINCE101
PRINCE101 used Ask the Experts™
on
Hi,

Am trying to populate a snapshot fact table, i decided to add a derived column to get the current date but then i need to write an expression to convert the date into integer value as my destination is integer. this is my expression (DT_WSTR,30)(DT_DBDATE)GETDATE() and i did the data conversation to four-bye signed which brought the error below.
any help on how to convert the current date to an integer so i can mapped the source and the destination correctly..
many thanks


[Get Today's Date [902]] Error: An error occurred while attempting to perform data conversion. The source type could not be converted to the destination type.
[Get Today's Date [902]] Error: SSIS Error Code

DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Get Today's Date" (902)" failed because error code 0xC0049062 occurred, and the error row disposition on "output column "TodaysDate" (908)" 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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Question: why use an int rather than a date?
Question: What is the format of your integer yyyymmddhhmm ??  yymmddhhmm ? yymmdd?

which style of date are you using date2? maybe converting it to a nvchar and then to an int may be what you need to do?
Olaf DoschkeSoftware Developer
Commented:
http://msdn.microsoft.com/en-us/library/ms141036.aspx is telling about DT_WSTR is a string. Your expression doesn't convert to int.

What should the integer be, you could eg convert 05/22/2012 to 20120522 or you could want something like days since 01/01/1900. Your conversion depends on what the integer needs to be. You should find out what integer value you need in the first place, then we can help with a conversion expression. But what you is going into unicode string (nvarchar). Of course that doesn't fit.

Bye, Olaf.
BI Consultant
Most Valuable Expert 2011
Commented:
Assuming you'd like to get an integer such as 20120523 (4 digits for the year, two for the month and two for the day), here's a possible solution.

Though it's technically possible using the Derived Column transformation, I would never do that.  It's much easier to achieve using .NET, so I recommend to use the Script Transformation.

The following code sample converts the incoming Date/DateTime column called YourDateColumn into an integer in the format specified.  The converted value is then stored in an output column called YourDateINT.

    DateTime dt = Row.YourDateColumn;
    Row.YourDateINT = Int32.Parse(dt.ToString("yyyyMMdd"));

If you need another format, you can just modify the formatter in the ToString() parameter.

Author

Commented:
Thank you all.. you guys are live saver.. glad to link with you guys

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial