0

I am trying to copy data from table1 to table2 in libreoffice base using a query like:

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";

column4 is a VARCHAR with value like '5/5/2015'. My problem is that column2 is a DATE type. So how can I cast column4 as DATE to be registred in column2?

Qiu
  • 5,651
  • 10
  • 49
  • 56
djamnazi
  • 1
  • 1
  • 1
  • Welcome to stackoverflow. Please read [ask]. Hint: Have you tried looking for conversion functions in your database documentation? – Zohar Peled May 15 '15 at 19:11
  • i'm find something like this SELECT CAST("5-5-2015", DATE) but when i replace "5-5-2015" by my column name that does not work. – djamnazi May 15 '15 at 19:38

1 Answers1

2

Yes you need to use the CAST command.

It looks like you are converting the string "column4" into a date NOT the data in the column. Try replacing the quotes with brackets like this...

INSERT INTO table1 ([column1], [column2], ...)
SELECT [column3], CAST([column4] AS DATE), ...
FROM table2;

or drop all the delimiters like this...

INSERT INTO table1 (column1, column2, ...)
SELECT column3, CAST(column4 AS DATE), ...
FROM table2;

You only need to enclose column names if they have spaces / special characters or include SQL keywords

abaldwin99
  • 903
  • 1
  • 8
  • 26
  • i tried what you suggested but it does not work. i think that it is necessary to enclosing all column names. And my column names have spaces. – djamnazi May 16 '15 at 10:13
  • If your column names have spaces then yes you need to enclose them. If it's still not working please post the whole query along with the error message. – abaldwin99 May 17 '15 at 11:19
  • here is my querry: INSERT INTO "personne" ("numsecu", "nom", "datenaissance") SELECT "Num Securité Sociale", "Nom", CAST[Date de naissance] FROM "donnee" – djamnazi May 18 '15 at 12:08