| View previous topic :: View next topic |
| Author |
Message |
darkshark
General User
Joined: 08 Feb 2010
Posts: 32
|
 Posted: Sun Apr 04, 2010 9:05 am Post subject: [SOLVED] How to convert a timestamp field to a date field |
 |
|
Hi,
i'm wondering if is it possible to convert a timestamp field to a date field (like DD/MM/YY)...
I know that i can "convert" a timestamp to a date with the to_char command, but in this case i lose the possibility to relate my date to a real date filed.
I wonder even if there's a way to group a timestamp filed in order to have a row for each day, i know that i can group using to_char, but even in this case the date that i get is a string and not a real date.
Thanks in advance.
Last edited by darkshark on Sun Apr 04, 2010 10:07 am; edited 1 time in total |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Sun Apr 04, 2010 9:43 am Post subject: |
|
|
darkshark:
You asked:
| darkshark wrote: | | i'm wondering if is it possible to convert a timestamp field to a date field (like DD/MM/YY) |
Yes . . . by using the CAST function.
I am making several assumptions, based on your post . . . and . . . for others reading this.
- You are using HSQL as your database engine ( you can confirm this by looking at the status line ( at the bottom ) after opening your OpenOffice Base file ).
- You do NOT want to 'permanently' change your table definition, but rather, just for a Query.
While creating your Query . . . if we assume a Table Name of "MyTable" and a Field Name of "MyTimeStampField" . . . use, on the Field line . . .
| Code: | | CAST( "MyTable"."MyTimeStampField" as "DATE") |
the above will return a value for a field with a database definition of DATE from the TIMESTAMP portion . . . and you could assign an Alias Name if you so desire.
Therefore, to return just this 'portion' ( date ) of the field, you could enter:
| Code: | | GROUP BY CAST( "MyTable"."MyTimeStampField" as "DATE" ) |
NOTE: You MAY have to 'format' the resulting date field as you want it - after running the Query - ( DD/MM/YY ) by selecting the Column and Format -> Date -> Select your desired date format.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
darkshark
General User
Joined: 08 Feb 2010
Posts: 32
|
| Posted: Sun Apr 04, 2010 10:09 am Post subject: |
|
|
It works perfectly.
Thank you for your precious advice. :D |
|
| Back to top |
|
|
|
