DSXchange

Hi All,

My source column QT_Travel data type is TimeStamp and data is like I mentioned below.

Nov 11 1996 12:00AM

I have to split this data into two columns with date and time as a separate column into target table.

Expected Output:

Date Column-1996-11-09
Time Column-12:00:00

I have Used TimeStamp to Time format(Columnname,"%hh:nn:%ss") for time column. But job got aborted due to some records not in the proper timestamp format. I need to reject the invalid timestamp records then I have to split the data and load the data to the target table

Any help would be appreciated.

So this "timestamp" source column (QT_Travel) is it actually a string? Not sure how else one would not get "proper timestamp value". If so, can you show us some of these bad timestamps?

TimestampToTime() function requires no format string. It requires that the input is a timestamp (not a string).

If your input is a string, use the Field() function to isolate the time component and StringToTime() to convert the format.

Thanks for your response.

I am receiving some data's are in below mentioned format

May 9 201 12:00AM It should be May 9 2011 12:00AM.

Like I mentioned above If I have any bad timestamp , I need to reject that data.

What is the data type of the source field? Where are you reading it from?

My source column data type is String and I am reading from the SQL Server.

OK, you have two questions to answer. One can be a constraint expression.
(1) Is the incoming string valid as a timestamp with a four digit year (IsValid() function)?
(2) Convert the string to a timestamp then extract the time portion, or extract the time portion and convert it to a time data type.

Hi Prabhu,

Please try this function.
IsValid('Date', StringToDate(<date Field>,"%yyyy%mm%dd") )=1 then its a valid date else its an invalid date.

Ray had put some comments, but i dont have a premium account i am not able to see it

Add to previous suggestions their is one more function IsValidTimestamp() try this it will genrate flag value, using this flag value u can filter the records

Just to point out the obvious: "May 9 201 12:00AM" is a perfectly valid date/time, it's just... old. So your validation will need to be more than just a simple validation check but will need business value range checks, it would seem.

... which is why I included "four digit year" in my specification.

Of course, just trying to point out that it may go beyond that.