DateTimestamp

g_rkrish · Post by **g_rkrish** » Thu Jul 26, 2007 11:56 am

ray.wurlod wrote:Reorder the date components, and format into a timestamp. Simple string manipulation.
Code: Select all
Col1[5,2] : "/" : Col1[7,2] : "/" : Col1[1,4]  ...[/quote]

Still i get the error saying 
[color=red]DBMS.CODE=-180, SQLSTATE=22007, [IBM][CLI Driver][DB2/AIX64] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007[/color]

chulett · Post by **chulett** » Thu Jul 26, 2007 12:09 pm

And therein lies the fundamental problem with how you approached this. You specified your 'required format' in your first post and so that's what people helped you build. The fact that it doesn't work is a whole 'nuther kettle of fish.

From what little I know about DB2, it seems to want date /time information in internal format. Can someone please confirm what this timestamp actually needs to look like in order to load properly into the target datetime field?

us1aslam1us · Post by **us1aslam1us** » Thu Jul 26, 2007 12:28 pm

Criag is right about it. Here is the how you can tweak this. Just remember what format you need at the output.

Code: Select all

OConv(IConv(Col1, "DYMD[4,2,2]"), "D/MDY[2,2,4]") : " " : OConv("Col2[1,2]:":":Col2[3,2]:":":Col2[5,2]", "MTS")

With the given i/p's, you will get
08/09/2007 06:10:00 am

g_rkrish · Post by **g_rkrish** » Thu Jul 26, 2007 12:33 pm

chulett wrote:And therein lies the fundamental problem with how you approached this. You specified your 'required format' in your first post and so that's what people helped you build. The fact that it doesn't work is a whole 'nuther kettle of fish.

From what little I know about DB2, it seems to want date /time information in internal format. Can someone please confirm what this timestamp actually needs to look like in order to load properly into the target datetime field?

Craig,

I have the same formatted timestamp in my target database so i gave my requirement but as i am new to DB2UDB i thought that might be standered format also..so i don't really able to figure where the problem lies.....

chulett · Post by **chulett** » Thu Jul 26, 2007 12:44 pm

g_rkrish wrote:I have the same formatted timestamp in my target database

No, you don't. Whatever query tool you are using to view that data is showing it to you in that format - and that's got nothing to do with how it is stored. Or what's needed to load it. Someone more familiar with DB2 will need to let us know what the stage is expecting.

g_rkrish · Post by **g_rkrish** » Thu Jul 26, 2007 12:50 pm

chulett wrote:
g_rkrish wrote:I have the same formatted timestamp in my target database
No, you don't. Whatever query tool you are using to view that data is showing it to you in that format - and that's got nothing to do with how it is stored. Or what's needed to load it. Someone more familiar with DB2 will need to let us know what the stage is expecting.

Ok got it..there where i got it wrong. thanks craig for your explanation....