Hi All,
I have timestamp information in a table. I want to extract the date portion
for insertion into another table.
I tried this
DECLARE
v_inventory_date DATE;
BEGIN
SELECT CAST(extract(year from min(scan_timestamp)) || extract(month from
min(scan_timestamp)) || extract(day from min(scan_timestamp)) AS date)
INTO v_inventory_date
FROM inventory.tbl_scanner;
But I get an error that the format is invalid. It seems that the extract is
returning a float. Because the float is only a single digit I get the error.
I am sure the day will give me the same error on the lower days. How can I
extract the complete date or get a two digit day and/or month?
Here is my table definition and the data.
IPADB=# \d inventory.tbl_scanner;
Table "inventory.tbl_scanner"
Column | Type | Modifiers
----------------+-----------------------------+------------------------
scan_timestamp | timestamp without time zone | not null
item_id | character varying(20) | not null
quantity | real | not null
employee_id | character varying(20) | not null
void | boolean | not null default false
Indexes:
"tbl_scanner_pkey" PRIMARY KEY, btree (scan_timestamp, item_id)
Foreign-key constraints:
"tbl_scanner_fkey1" FOREIGN KEY (item_id) REFERENCES
peachtree.tbl_item(id) ON UPDATE CASCADE ON DELETE RESTRICT
"tbl_scanner_fkey2" FOREIGN KEY (employee_id) REFERENCES
peachtree.tbl_employee(id) ON UPDATE CASCADE ON DELETE RESTRICT
IPADB=# SELECT * FROM inventory.tbl_scanner;
scan_timestamp | item_id | quantity | employee_id | void
---------------------+---------+----------+-------------+------
2005-01-19 18:46:00 | 004 | 11 | 116 | t
2005-01-19 18:45:00 | 004 | 10 | 116 | t
(2 rows)
Kind Regards,
Keith
