TimeStamp
Moderators: chulett, rschirm, roy
TimeStamp
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.
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.
prabakaran.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
If your input is a string, use the Field() function to isolate the time component and StringToTime() to convert the format.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
(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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 18
- Joined: Tue Dec 25, 2012 10:39 pm
- Location: Detroit,MI
-
- Participant
- Posts: 15
- Joined: Wed May 02, 2012 3:38 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: