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

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

• wlaulner

  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

  
  
  

• Luis Esteban Valencia Muñoz

  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

  
  
  

• GunaChinna

  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

  
  
  

• Darren Tao

  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.

   

  
  
  

• shaunhw

  Thanks Jami

  
  
  

• Ninerh

  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

  
  
  

• Drew Yallop

  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

  
  
  

• Luo Cao

  Conditional Operator (http://msdn2.microsoft.com/en-us/library/d38e6890-7338-4ce0-a837-2dbb41823a37.aspx)

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

  
  
  

• cverdon

  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

  
  
  

• Raja Prabu

  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

  
  
  

• Tryin2Bgood

  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

  
  
  

• srfitz2000

  Its simply a string manipulation problem.

  Look at:

  Concatenation

  SUBSTRING()

  -Jamie

  
  
  

• Anarchy

  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

  
  
  

• Dylan Smith

  "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

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