OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calculate difference between two date/time columns

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
ZLOCKO
General User
General User


Joined: 19 Jan 2008
Posts: 5

PostPosted: Sat Jan 19, 2008 10:42 am    Post subject: Calculate difference between two date/time columns Reply with quote

Maybe this is a very stupid question, but I am new in OO Rolling Eyes
How to set up the column in Query that calculate difference between two Date and time columns (A and B) i OOBase?
column A - field type Date/Time (Timestamp), format Numeric, 6 decimal places
column B - field type Date/Time (Timestamp), format Numeric, 6 decimal places
column C=B-A (format - Numeric)
Field A and B are entered in Form in Date/Time type.
In table those two columns are shown in numeric format (as number with 6 decimal places).
But when I create Query based on A and B and put the column for B-A when I start the query I got a message Wrong Data Type.


Thanks in advance
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sat Jan 19, 2008 1:03 pm    Post subject: Reply with quote

ZLOCKO:

You have asked:
ZLOCKO wrote:
How to set up the column in Query that calculate difference between two Date and time columns (A and B) i OOBase?

This link will show the date and time 'functions' available with HSQL ( the embedded OpenOffice database):

http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#Date_and_Time_Functions

The DATEDIFF function may be of help.
Quote:
DATEDIFF(string, datetime1, datetime2)

Returns the count of units of time elapsed from datetime1 to datetime2. The string indicates the unit of time and can have the following values 'ms'='millisecond', 'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. Both the long and short form of the strings can be used.

DATEDIFF('dd', '2007-08-01', '2007-09-01' ) = 31

For example, see the links below:
  1. http://www.oooforum.org/forum/viewtopic.phtml?t=58876
  2. http://www.oooforum.org/forum/viewtopic.phtml?t=45245

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
View user's profile Send private message
ZLOCKO
General User
General User


Joined: 19 Jan 2008
Posts: 5

PostPosted: Sun Jan 20, 2008 3:33 am    Post subject: Reply with quote

Thanks a lot... I'll try.
Thanks again.


-----------------

27/04/2008
Thanks.
It is working perfectly. Thank You!
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group