Error trying to CAST a timestamp with sun ODBC-JDBC bridge and ireport


I am trying to make a unique (but repeatable) reference by concatenating a string with a timestamp as part of an Access 97 query via the Sun ODBC-JDBC connector wit iReport:

tbl_PET_Pet.Pet_Time_Updated + tbl_PET_Pet.PET_Pet_Reference as uniqueid

The report complains that the textfield cannot be cast from a timestamp to a string so I have tried various permutations in the query, such as:

cast (tbl_PET_Pet.Pet_Time_Updated as varchar(20)) + tbl_PET_Pet.PET_Pet_Reference as uniqueid,

But the query won't build - Missing Operator.

Am I getting the syntax wrong for an ODBC query?

Any ideas?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

A timestamp is equivalent to a binary(8) column.  So you most likely have to convert it to a number first.

CAST(CAST(tbl_PET_Pet.Pet_Time_Updated as bigInt) as varchar(20)) + tbl_PET_Pet.PET_Pet_Reference as uniqueid
nkendrickAuthor Commented:
Thanks but that still gives the same error message.
I think you need to past the entire query.

Also, what is the backend database server?
nkendrickAuthor Commented:
Thanks all - I have resolved this by concatenating the two required fields in the report output field rather than at query time and this works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.