Ask Your Question

Getting the date section of timestamp

asked 2018-11-04 22:39:30 +0100

tahasozgen gravatar image

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.

edit retag flag offensive close merge delete




What database are you using? HSQL embedded (default), Firebird embedded (experimental default), external such as MySQL, PostgreSQL, etc.

Ratslinger gravatar imageRatslinger ( 2018-11-04 23:40:49 +0100 )edit

Hi, I am using HSQL embedded (default).

tahasozgen gravatar imagetahasozgen ( 2018-11-05 01:29:40 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-11-05 01:51:26 +0100

Ratslinger gravatar image

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:


I point this out to help understand this SQL.

So to extract just the date in the format you want using a data field:


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.

edit flag offensive delete link more


I may turn into the Firebird. Is the firebird version of this same?

tahasozgen gravatar imagetahasozgen ( 2018-11-05 02:12:47 +0100 )edit

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?.

Ratslinger gravatar imageRatslinger ( 2018-11-05 02:29:40 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-04 22:39:30 +0100

Seen: 489 times

Last updated: Nov 05 '18