string to timestamp error

[dsdevper]

Hi My input field is Timestamp but since i was missing the milliseconds from the field i am converting it into char field to get the missing milliseconds.
so

My sql : select convert(char(23),update_dt,9)) as update_dt from table.

My source Data value is

Feb 17 1995 12:00:00:000AM(Varchar (26))

when i am trying ti convert it using

StageVar1 = Trim(input.coloumn(Varchar(26))

StringToTimestamp(StageVar1,'%yyyy-%mm-%dd %hh:%nn:%ss.6') i am getting following error.


APT_CombinedOperatorController(1),0: Data string 'Feb 17 1995 12:00:00:00' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss.6': an integer was expected to match tag %yyyy.
APT_CombinedOperatorController(1),0: Conversion error calling conversion routine timestamp_from_string data may have been lost
APT_CombinedOperatorController(1),0: Data string 'Feb 17 1995 12:00:0' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': an integer was expected to match tag %yyyy.
APT_CombinedOperatorController(1),0: Conversion error calling conversion routine timestamp_from_string data may have been lost

so i tried

StringToTimestamp(StageVar1,'%mon %dd %yyyy %hh:%nn:%ss.6') then i am getting folllowing error.


APT_CombinedOperatorController(1),0: Data string 'Feb 17 1995 12:00:00:000AM' does not match format '%mon %dd %yyyy %hh:%nn:%ss.6': an integer was expected to match tag %m.
APT_CombinedOperatorController(1),0: Conversion error calling conversion routine timestamp_from_string data may have been lost
APT_CombinedOperatorController(1),0: Data string 'Feb 17 1995 12:00:0' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': an integer was expected to match tag %yyyy.

Please any suggestions..

[anbu]

Your format string does not match with your input. %yyyy expects integer but you have "Feb" in your input. Check the documentation for appropriate format string

[chulett]

It's %mmm rather than %mon from what I recall and you'll need six zeroes for %ss.6 and you've only got three.

[dsdevper]

Hi Anbu,

Input data 'May 12 2003 12:19:52:256'

I changed the format to

StringToTimestamp(StageVar1,'%mmm %dd %yyyy %hh:%nn:%ss:%SSS')

now i am getting Out put as : May 12 2003 12:19:52.

i am missing milliseconds from the output.

so i tried

StringToTimestamp(StageVar1,'%mmm %dd %yyyy %hh:%nn:%ss.6')

i am getting error:

APT_CombinedOperatorController,0: Data string 'May 12 2003 12:19:52:256' does not match format '%mmm %dd %yyyy %hh:%nn:%ss.6': missing the fractional part of the field expected by tag %ss.6.

[BradMiller]

Do you need the field to be Char? Why not leave it as a Timestamp (length 26) and set the Extended property to "Microseconds"?

[dsdevper]

Craig :

I tried as you said replaced %ss.6with 00000

StringToTimestamp(StageVar1,'%mmm %dd %yyyy %hh:%nn:%00000')

job failed saying

APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%mmm %dd %yyyy %hh:%nn:%00000" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid format [%mmm %dd %yyyy %hh:%nn:%00000] used for string_from_time type conversion.

Also i tried StringToTimestamp(StageVar1 '%mmm %dd %yyyy %hh:%nn:%ss:000000'):


APT_CombinedOperatorController,0: Data string 'May 12 2003 12:19:52:256' does not match format '%mmm %dd %yyyy %hh:%nn:%ss.000000': the data string has fewer characters than expected.

i tried with only 3 zeros then i am not getting milli seconds.

[chulett]

Sorry, but that's not what I said. Your timestamp has has a precision of three so the mask needs to be %ss.3 rather than %ss.6.

[dsdevper]

Do you need the field to be Char? Why not leave it as a Timestamp (length 26) and set the Extended property to "Microseconds"?

I am converting Char to time stamp .i just did what you said i set it to timestamp(26) and micro seconds then its returning me microseconds as 000000 instead of real values i am getting 2003-05-12 12:19:52.000000 it should be 2003-05-12 12:19:52:256

Craig:

i tried with %ss.3

i am getting following error

APT_CombinedOperatorController,0: Data string 'May 12 2003 12:19:52:256' does not match format '%mmm %dd %yyyy %hh:%nn:%ss.3': missing the fractional part of the field expected by tag %ss.3.

[ray.wurlod]

You have a colon between seconds and fractional seconds in your data, but your format string specifies a dot. You need to make them match by changing one or the other.

[chulett]

As noted, "May 12 2003 12:19:52:256" would typically be displayed like so: "May 12 2003 12:19:52.256" hence the "%ss.3" advice. But regardless, they need to match.

[dsdevper]

Craig/Ray For "May 12 2003 12:19:52:256"

If i use " ss:3" i am not getting the milliseconds i am getting "May 12 2003 12:19:52".

If i use " ss.3 " i am getting "does not match format '%mmm %dd %yyyy %hh:%nn:%ss.3': missing the fractional part of the field expected by tag %ss.3." error.

Any suggestions...

[chulett]

That is your actual target here?

[Vidyut]

Hi
Try this. hope this helps

StringToTimestamp(DSLink442.a,'%mmm %dd %yyyy %hh:%nn:%ss:%SSS')

Make the target column as Timestamp Extended