I run:

SELECT TIMESTP, CAST(TIMESTP AS STRING) FROM ….

I get back :

2006-05-11 10:48:10.0, 2006-05-11-10.48.10.000000

Notice the extra – between the month and the hour… The bleeding database does when we try and use it in a select. Any way of controlling the format of the string when converting from Timestamp to a string? The same thing happens when we use Convert.

It is running in Metamatrix through a JDBC driver onto a DB2 database.

Good grief.

Look, I am stuck in Switzland so I have to do something. Basically for some reason we have to convert a timestamp into a string and store it in a table. We then extract it from that table, convert it back into a time stamp and use that to select from another table. It sticks the bloody dash in there so never finds any matches from the select.

Please can someone do my work for me and resolve this.

looked on google and it seems people have done it by either string manipulation or building up a string from the component date parts. not nice.

but I aint no DB2 expert, just bored

I would build up the component parts and concatenate them together…

can you not just split it in 2 and then join without the -. Left(your_string,x)

right(your_string,x)

then left||right??

upgrade to oracle 🙂

Get an expensive consultant in who knows what they're doing.

Basically for some reason we have to convert a timestamp into a string and store it in a table. We then extract it from that table, convert it back into a time stamp and use that to select from another table. It sticks the bloody dash in there so never finds any matches from the select.

Now that just seems stoopid.

I'm with soma_rich, split it, then rejoin it. Not the best solution, really you should be looking why its putting a "-" there.

But i could be talkin out me arse! I aint done programming for 10+ years!

Try concatenating using YEAR, MONTH etc. functions:

YEAR (current timestamp)
MONTH (current timestamp)
DAY (current timestamp)
HOUR (current timestamp)
MINUTE (current timestamp)
SECOND (current timestamp)
MICROSECOND (current timestamp)

Use something better than DB2. For Gods sake man, this some sort of inventory system for Noahs Ark?

as said – split and rejoin – if the string is uniform in length this should work:

SUBSTRING(CAST(TIMESTP AS STRING),1,10)+' '+SUBSTRING(CAST(TIMESTP AS STRING),12,100)

from the tech document it looks like the extra dash is default behaviour

Use something better than DB2. For Gods sake man, this some sort of inventory system for Noahs Ark?

Blah blah blah, you'll be recommending Oracle next. My client's got Oracle, 2500 bloody tables for the main user, the other user apparently doesn't know what a primary key is, so forget about referential integrity…

I've seen better Access databases.

why do you want to store it as a string?

you want a PICK-type database 😉

in PICK, dates are plus or minus the number of days since (or before) 31/12/1967; times are seconds after midnight. And the system has operators that convert them into something readable in whatever format you like.

Tables? What are they? PICK has 'files' instead of Tables, and individual
records rather than 'rows'. Each record is multidimensional too…

When you to string the date can you not set the format of the date as a parameter of the to string function, or am I just thinking too much like a pl/sql developer?
😛