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?
nkendrickConnect With a Mentor Author 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.
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?
All Courses

From novice to tech pro — start learning today.