Hello,

i have a DateTime as string ('06.11.2004 16:29:18') and search for an sql-statement to insert it into my table.

I thought it must lool like:

insert into mytable(FieldString1, FieldString2, FieldTimestamp)
values ('StringNr1', 'StringNr2', '06.11.2004 16:29:18')

It doesn't work.

I tried to find out what is the right syntax and read in the manuel and also in this forum.
My english isn't very well but i found something like that:

insert into mytable(FieldString1, FieldString2, FieldTimestamp)
values ( 'StringNr1', 'StringNr2', CAST('06.11.2004 16:29:18')AS TIMESTAMP)

It also doesn't work.

Please give me a help.

Thank you

Martin


>i have a DateTime as string ('06.11.2004 16:29:18') and search for an sql-statement to insert it into my table.
>
>I thought it must lool like:
>
>insert into mytable(FieldString1, FieldString2, FieldTimestamp)
>values ('StringNr1', 'StringNr2', '06.11.2004 16:29:18')

You have two problems:

In ElevateDB the datetime string should be 2004-11-06 16:29:18 (the date portion is in sql format)

Secondly if you supply it as a string then you must preface it with the keyword TIMESTAMP so your statement would become

insert into mytable(FieldString1, FieldString2, FieldTimestamp)
values ('StringNr1', 'StringNr2', TIMESTAMP  '2004-22-06 16:29:18')

There are some helpers built into the engine eg DateTimeToSQLStr so you can call those to get the correctly formatted string to pass to the INSERT command. Alternatively you can use string sliceing to build the right command eg

insert into mytable(FieldString1, FieldString2, FieldTimestamp)
values ('StringNr1', 'StringNr2', TIMESTAMP SUBSTR('06.11.2004 16:29:18',7,4)+'-'+SUBSTR('06.11.2004 16:29:18',4,2)+'-'+SUBSTR('06.11.2004 16:29:18',1,2)+' '+SUBSTR('06.11.2004 16:29:18',,11,8))

I think I got the string positions right but its untested.

Roy Lambert

That's it.

Thank you