none
How to Convert String Data type to DateTime in Derived Column Control In SSIS Package

    Question

  • Hi ,

    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

    Jeyakumar.M

      

     

     

    Monday, June 26, 2006 7:34 AM

Answers

All replies

  • Hi,

    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?

    HTH.

    -Jamie

     

    Monday, June 26, 2006 8:11 AM
    Moderator
  • Hi

     

    Thanks for your reply. The Input Column What ever u told above the same Format i am passing the source column.But that format  Data type is String . YYYYMMDD Hr:MM:SS

     

     

    That column how to convert into DateTime Format in the Destination

     

     

    Monday, June 26, 2006 8:53 AM
  • I've just checked and it needs to be

    YYYY-MM-DD HH:MIS:SS

    and not

    YYYYMMDD HH:MIS:SS

    Which is what I said before!!

     

    My apologies!!!

    -Jamie

     

    Monday, June 26, 2006 9:22 AM
    Moderator
  • Hi Jami

     

    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

    Jeyakumar

    Chennai

     

     

     

    Monday, June 26, 2006 11:04 AM
  • Its simply a string manipulation problem.

    Look at:

    Concatenation

    SUBSTRING()

     

    -Jamie

     

    Monday, June 26, 2006 11:15 AM
    Moderator
  • Thanks Jami

     

    Monday, June 26, 2006 11:56 AM
  • Hi Jami

     

     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

    Jeyakumar.M

    Chennai

     

    Tuesday, June 27, 2006 5:58 AM
  • If you mean a SQL Server Tmestamp, then that has nothing to do with dates and times.

    If you mean DT_DBTIMESTAMP, then it works fine for me.

    Can you be clearer what your problem is?

    Tuesday, June 27, 2006 7:02 AM
    Moderator
  • hi jami

     

    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 .

     

    for sample

    !ISNULL(PriceTimeStamp) Then

    SubString(PriceTimeStamp)

     

    How to achiev this condtion in the Derived Column control

     

    Thanks & Regards

    Jeyakumar.M

     

     

     

    Tuesday, June 27, 2006 7:33 AM
  • Conditional Operator (http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37.aspx)

    !ISNULL(ABC) ? SUBSTRING(ABC) : "What you want for NULL"

    Tuesday, June 27, 2006 7:38 AM
    Moderator
  • Hi ,

     

    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

    Jeyakumar.M

    chennai

     

    Wednesday, June 28, 2006 5:47 AM
  • "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.

    -Jamie

     

     

    Wednesday, June 28, 2006 7:49 AM
    Moderator
  • 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.

     

    - Venkat

    Thursday, August 17, 2006 2:22 PM
  • 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
    Thursday, August 17, 2006 2:35 PM
    Moderator
  • 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.

     

    thanks,

    Venkat

    Thursday, August 17, 2006 2:41 PM
  • 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 [248]: 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.".

    Any suggestions?

     

    Thursday, September 07, 2006 7:38 PM
  • I am having the same problem. Can any one get the solution. Please post it.

    I will be very appriciate.

    Thursday, September 07, 2006 9:32 PM
  • 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...

    Thursday, October 25, 2007 4:01 PM
  • 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:mmTongue Tieds 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

    Tuesday, April 01, 2008 6:27 PM
  • Hi,

     

    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:MMTongue TiedS

     

    Hope that helps

     

    Flo

    Tuesday, July 29, 2008 10:17 AM
  • Flo solution works.

    My situation was very similar, I only had date columns that could be null.  (YYYYMMDD).

    Code:

    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
    Monday, March 16, 2009 5:51 PM