TimeStamp

praburaj · Post by **praburaj** » Thu Dec 27, 2012 8:19 am

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.

chulett · Post by **chulett** » Thu Dec 27, 2012 9:27 am

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?

ray.wurlod · Post by **ray.wurlod** » Thu Dec 27, 2012 2:09 pm

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.

praburaj · Post by **praburaj** » Thu Dec 27, 2012 11:57 pm

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.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 28, 2012 12:23 am

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

praburaj · Post by **praburaj** » Fri Dec 28, 2012 12:41 am

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

ray.wurlod · Post by **ray.wurlod** » Fri Dec 28, 2012 3:28 am

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.

harishkumar.upadrasta · Fri Dec 28, 2012 6:27 am

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

venkateshrupineni · Post by **venkateshrupineni** » Fri Dec 28, 2012 7:21 am

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

chulett · Post by **chulett** » Fri Dec 28, 2012 7:45 am

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.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 28, 2012 3:11 pm

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

chulett · Post by **chulett** » Fri Dec 28, 2012 3:27 pm

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