string to timestamp error
Moderators: chulett, rschirm, roy
string to timestamp error
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..
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..
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.
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.
-
- Premium Member
- Posts: 87
- Joined: Mon Feb 18, 2008 3:58 pm
- Location: Sacramento, CA
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.
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.
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:256BradMiller wrote:Do you need the field to be Char? Why not leave it as a Timestamp (length 26) and set the Extended property to "Microseconds"?
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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...
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...
Re: string to timestamp error
Hi
Try this. hope this helps
StringToTimestamp(DSLink442.a,'%mmm %dd %yyyy %hh:%nn:%ss:%SSS')
Make the target column as Timestamp Extended
Try this. hope this helps
StringToTimestamp(DSLink442.a,'%mmm %dd %yyyy %hh:%nn:%ss:%SSS')
Make the target column as Timestamp Extended