CAST and timestamp

Hi All,

I am receiving a quantity as text and a seperate date and time as text. The
quantity is written into a varchar(20) column and the date and time are
written into char(8) and char(6) columns respectively. I would like to
convert the information for proper storage. When I perform a query to CAST
the quantity into a real and CAST the two text columns into a timestamp column
I receive errors. I have tried to find the documentation on the CASTs to no
avail. I need to know how to convert the quantity peroperly and I would
prefer to not specify the time zone and use the value from the host computer.
Any information would be appreciated.

IPADB=# \d data_transfer.tbl_inventory_scanner
Table "data_transfer.tbl_inventory_scanner"
Column | Type | Modifiers
-------------+-----------------------+-----------
employee_id | character varying(20) |
item_id | character varying(20) | not null
quantity | character varying(20) |
scan_date | character(8) | not null
scan_time | character(6) | not null
Indexes: tbl_inventory_scanner_pkey primary key btree (scan_date, scan_time,
item_id)

IPADB=# SELECT * FROM data_transfer.tbl_inventory_scanner;
employee_id | item_id | quantity | scan_date | scan_time
-------------+---------+----------+-----------+-----------
1116A | SAC38 | 55 | 20041220 | 160933
1116A | SEB12 | 555 | 20041220 | 160947
1116A | SEBM106 | 888 | 20041220 | 160953
1116A | B346.0 | 555 | 20041220 | 161003
1116A | B346.5 | 888 | 20041220 | 161011
(5 rows)

IPADB=# SELECT CAST(scan_date || scan_time AS timestamp) AS datetime_timestamp
FROM data_transfer.tbl_inventory_scanner;
ERROR: Cannot cast type character to timestamp without time zone

IPADB=# SELECT employee_id, item_id, CAST(quantity AS float4) AS quantity FROM
data_transfer.tbl_inventory_scanner;
ERROR: Cannot cast type character varying to real

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com