Hi There;
I have a Libreoffice base database. I have a timestamp column in a table. In a query, I need the date section of the timestamp, say, “30.09.18 02:18” is a timestamp. I need a function that returns “30.09.18”. Is there any function to that?
Thanks in advance.
Hello,
What database are you using? HSQL embedded (default), Firebird embedded (experimental default), external such as MySQL, PostgreSQL, etc.
Hi, I am using HSQL embedded (default).
Hello @tahasozgen,
Will start with the timestamp you state in your question. Just want you to understand that is not a valid timestamp. That is the display format chosen by your for the timestamp. The timestamp is actually stored in the DB as:
YYYY-MM-DD HH:MM:SS
I point this out to help understand this SQL.
So to extract just the date in the format you want using a data field:
Select TO_CHAR(YOUR_TIMESTAMP, 'DD.MM.YY' ) AS "A_NAME" from "YOUR_TABLE"
To extract just the date in the format you want using a literal:
Select TO_CHAR(cast('2018-09-30 02:18' AS TIMESTAMP), 'DD.MM.YY' ) AS "A_NAME" from "YOUR_TABLE"
Also, the reason for asking for the type of DB used is that SQL can differ in databases. Understand HSQLDB is old & is in the process of being replaced with Firebird 3 embedded.
I may turn into the Firebird. Is the firebird version of this same?
If you are contemplating Firebird, please read my answer in this post → Firebird Migration Expectations.
And as for your question see this page → Why can’t I extract date or time from timestamp?.