string to time

new_ds_man · Post by **new_ds_man** » Mon Feb 13, 2012 9:20 am

Hi All,

I have a time value in format HHMMSSC, HH is hours, MM is minutes, SS is seconds and C is fraction of a second.
I need to convert this to a valid time format.

If i have 1212124 from the source, the result format should be 12:12:12.4.
I tried StringToTime(sourcecol,"%hh%nn%ss"), which gives the value 12.12.12

Can someone please provide help.

Thanks in advance.

jwiles · Post by **jwiles** » Mon Feb 13, 2012 9:31 am

Date and time values are stored in an internal binary format. The character representation you are seeing after using StringToTime() is likely the default format for your DataStage installation or project, which is used when you peek the data, use TimeToString() without a format string or drop the Time column directly into a char/varchar column without a wrapping funciton. This has been discussed many times in the forum.

If you need to convert the time back to a character string with a certain format, use TimeToString() with the proper format string.

The same concept is true of ANY non-character datatype...when you view the data with peek or convert it to char/varchar without using format strings, you are seeing a character representation creating using the intrinsic datatype-to-character conversions and system/project default formats.

Regards,

new_ds_man · Post by **new_ds_man** » Mon Feb 13, 2012 9:40 am

Thanks for the quick reply James.
The source column that is sending the time value is a char(7).

Please bear with me on below.
If i understood you correct, are you suggesting that i move this to a char/varchar with a format string.
will this preserve the time format still?

Thanks.

pandeesh · Post by **pandeesh** » Mon Feb 13, 2012 10:47 am

have you tried

Code: Select all

StringToTime(sourcecol,"%hh%nn%ss%c")

?

jwiles · Post by **jwiles** » Mon Feb 13, 2012 12:20 pm

No, I am attempting to help you understand the results you are seeing and to encourage you to understand what your target's requirements actually are. From that knowledge you can then determine what you need to do within your job logic.

You really need to come at it from the point of what your target for the time data is. What data type (SQL Type) does your target require? Does it require a character string? Does it require a time data type?

- Are you writing data to a sequential file for a later process/application to consume? If so, then you are probably needing a character string as your final output. You can use StringToTime() to convert your source to a time data type, then TimeToString() to convert it back to a character string for output (in the required format).

- Are you writing directly to a database using a DB Stage? If so, what is the data type of the target column? Char/Varchar/Time/Date/Timestamp. If Char/Varchar, then the same options as above. If Time/Date/Timestamp, then you can likely convert to the same data type, or a timestamp, and provide that directly to the DB stage.

Understanding what your target is and it's specific requirements for the data is important to properly designing your job.

Regards,

new_ds_man · Post by **new_ds_man** » Mon Feb 13, 2012 12:51 pm

pandees, i tried that, says invalid format...Invalid Format [%hh%nn%ss%c] used for string_from_time type conversion.

James,
The requirement is a little different in this case. I am just trying to understand why the conversion would not work for 7 length time field (Ex: 1010103).

Thanks for the replies.

jwiles · Post by **jwiles** » Mon Feb 13, 2012 1:02 pm

Your original format string didn't include a milliseconds option. %C is NOT the correct option for specifying milliseconds. Time format strings are well-documented:

http://publib.boulder.ibm.com/infocente ... rmats.html