Ask Your Question
1

Getting the date section of timestamp

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

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

Comments

1

Hello,

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 +0200 )edit

Hi, I am using HSQL embedded (default).

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

1 Answer

Sort by » oldest newest most voted
0

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

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:

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.

edit flag offensive delete link more

Comments

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

tahasozgen gravatar imagetahasozgen ( 2018-11-05 02:12:47 +0200 )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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 92 times

Last updated: Nov 05 '18