I am Using Derived column between Source and Destination Control. the Source input column PriceTime is String Data type. but in the Destination is should be a DATE TIME column. How to Convert this string to DateTime in the Derivied Column Control.
I already tried to in the Derived column control
PRICEDATETIME <add as new column> ((DT_DBTIMESTAMP)priceDateTime) database timestamp [DT_DBTIMESTAMP]
But still throwing Error showing type case probelm
Pls help me on this
Thanks & Regards
This isn't an easy thing to do. The vagaries of casting between strings and datetimes (e.g. 04/05/06 - Is that 5th April or 4th May? It depends where in the world you are) means that SSIS doesn't provide direct support of this type of casting and you can experience the problems that you are currently having.
The way around it is to parse out the various constituent parts of your datetime, concatenate them together in the form YYYYMMDD HH:MI:SS, and then do you cast operation.
Out of interest, what format are your dates in prior to your cast operation?
Thank you for ur solution for my problem . When i gave sample String format wahtever u told .now its working fine. But my source file contain only YYYYMMDD HH:MIS:SS .
how to convert this format in to standard format(YYYY-MM-DD HH:MIS:SS) . we can using String function manually or anyother way?
Thanks & Regards
When i was gave the String to Date Format in the follwing substring is workinge fine.
(DT_DATE)(SUBSTRING(priceDateTime,5,2) + "-" + SUBSTRING(priceDateTime,7,2) + "-" + SUBSTRING(priceDateTime,1,4))
But the same time the destination column type is Timestamp its problem in type cast.how to give the substring for TimeStamp.
Thanks & regards
Data type conversion from String to Date is working fine . But one of the Column (PriceTimeStamp) allow Null column.that time how to apply the Substring Format in the Derived Column.how to check the condition in the Derived Column control .
How to achiev this condtion in the Derived Column control
Thanks & Regards
Conditional Operator (http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37.aspx)
!ISNULL(ABC) ? SUBSTRING(ABC) : "What you want for NULL"
Thanks for ur mail. In the Derived column control i applied the above conditional operator.Still i am strugling with syntax error. here i past my code pls correct the Syntax.
ISNULL(accruedIntSettleDate) ? 0 : ((DT_DATE)(SUBSTRING(lastPriceDateTime,5,2) + "-" + SUBSTRING(lastPriceDateTime,7,2) + "-" + SUBSTRING(lastPriceDateTime,1,4)))
thanks & Regards
"0" is a TD_I4
"((DT_DATE)(SUBSTRING(lastPriceDateTime,5,2) + "-" + SUBSTRING(lastPriceDateTime,7,2) + "-" + SUBSTRING(lastPriceDateTime,1,4)))" is a DT_DATE
The second and third parts of the conditional operator need to pass out values of the same type. It looks as though you need a default date value to pass out if the incoming data is null.
I have a similar situation, I was getting DateString in similar format and, if my date is missing there was a ‘0’,
So I can’t use substring. So I used a conditional split, and separated out the zero value records into one data flow then used same above logic for the datestring rows. And that flow goes to the target.
For the rows with ‘0’ as datestring, I inserted another data flow, just same transformations as before including the target, except the ‘column derive task’, and in the target just ignored to write the ‘0’ date column to the target. It worked since my target datetime was a nullable column.
Is your logic not this-
if value is 0, insert null value
if value is not 0, then convert to a date, and insert date value.
If so, why not do this all in one derived column expression. Using the conditional operator you could test for the 0, and assign null, or do the conversion. It would seem much simpler and no doubt faster.
- Proposed as answer by vinay.joseph Wednesday, June 30, 2010 4:13 AM
Yes, that is the final logic I am implementing, but I am using transformations to do that, finally when I ignored to insert '0' into the target, that is what happens, a null is inserted into the target. I may be using more transformations in the data flow.
Hi, I sure hope someone will be able to steer me in the right direction.
I have not been having much luck converting a DT_WSTR to DT_DBTIMESTAMP.
Input from a flat file is two fields, startdate and starttime. I'm concatenating them to get startdatetime in a derived column, (DT_DBTIMESTAMP)(SUBSTRING(RetrievalDate,6,2) + "/" + SUBSTRING(RetrievalDate,9,2) + "/" + SUBSTRING(RetrievalDate,1,4) + " " + StartTime)
RetrievalDate is input yyy-mm-dd StartTime is input HH:MM:SS:MMM. The error is "Error: 0xC0049064 at Data Flow Task, Date Time Conversions : An error occurred while attempting to perform a type cast."
There was an error with input column "RetrievalStartDateTime" (631) on input "OLE DB Destination Input" (582). The column status returned was: "The value could not be converted because of a potential loss of data.".
In the time part, is it upto milliseconds? I mean, do u have value in time like.. 14:45:53.456? In that case, even I am facing the same problem.. However, if MMM part of the time is not relavant to you, then you can substring the time till 8 characters. i.e.
(DT_DBTIMESTAMP)(SUBSTRING(RetrievalDate,6,2) + "/" + SUBSTRING(RetrievalDate,9,2) + "/" + SUBSTRING(RetrievalDate,1,4) + " " + SUBSTRING(StartTime,1,8)).
This will work, but will remove millisecond part from the time. In my case I need milliseconds as well, but I am strugling to get that...
I am competent with excel but I just don't have the programmer jeans to be able to pull this one. It is very similar to what the original question was.
How to convert hh:mms to just seconds using the Format function. With let's say 6 decimal points?
Please advise with directions, I have almost 150 that I will need to do by hand. Would probably be a mind numbing hour for me. PLEASE HELP
Get the same problem and to manage string to datetime I finally use this Expression:Code Snippet
LEN(TRIM(priceDateTime)) == 0 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(priceDateTime,1,4) + "-" + SUBSTRING(priceDateTime,5,2) + "-" + SUBSTRING(priceDateTime,7,2) + " " + SUBSTRING(priceDateTime,10,8))
where priceDateTime is originally a varchar in format YYYYMMDD HH:MMS
Hope that helps
Flo solution works.
My situation was very similar, I only had date columns that could be null. (YYYYMMDD).
LEN(TRIM([DateColumn])) == 0 ? NULL(DT_DATE) : ((DT_DATE)(SUBSTRING([DateColumn],5,2) + "-" + SUBSTRING([DateColumn],7,2) + "-" + SUBSTRING([DateColumn],1,4)))
- Edited by Huntsman85 Monday, March 16, 2009 6:08 PM Update