[Solved] Date Comarison in OOBase Query

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Date Comarison in OOBase Query

Postby shrikant_US » Fri May 30, 2008 11:05 am

I have OpenOffice 2.4 installed on my laptop.

I have a table called "TblEmployee" in OOBase which has following fields:
- EmpCode - Varchar (10) : (Employee Code)
- EmpName - Varchar (50) : (Employee Name)
- DOJ - Date : (Date of Joining)

I have written a query "Select EmpCode, EmpName, DOJ from TblName" to get the basic result from the table. Moreover, I would like to have an additional column in the query called "TotalExperience" that would show the Number of years (in decimal figure) since the DateOfJoining of the employee. I understand, there is a system variable called "CURRENT_DATE", which returns today's date (i.e. system date). But need to know how to use this "CURRENT_DATE" to get the expected result.
Or else, please let me know if there is any other alternative command / function to get the result.

Last edited by shrikant_US on Fri May 30, 2008 12:49 pm, edited 1 time in total.
Posts: 16
Joined: Fri May 30, 2008 6:47 am

Re: Date Comarison in OOBase Query

Postby Villeroy » Fri May 30, 2008 11:52 am

Did you create the database in Base and from scratch? Does the statusbar show "hsqldb engine" or something like that? The hsqldb engine which is integrated in OOo since version 2 is documented (rather technically) at http://hsqldb.org/doc/guide/ch09.html where you find function
Code: Select all   Expand viewCollapse view
DATEDIFF(string, datetime1, datetime2)[2]

    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.

to get the number of time-units between two dates.
Unfortunately there is no inverse function to get a date from another date and a number of time-units.
Hope this helps
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

<Solved> : Date Comarison in OOBase Query

Postby shrikant_US » Fri May 30, 2008 12:43 pm


The query is resolved. I could get the expected result by using "CAST" Function.
Forwarding the complete query for future reference.

SELECT "TblEmployee"."EmpCode", "TblEmployee"."EmpName", "TblEmployee"."DOJ",
( cast(CURRENT_DATE as decimal(10,2)) - cast("TblResource"."DOJ" as decimal(10,2)) ) AS "Total_EXP"
FROM "TblEmployee"

Thanks & Regards,
Posts: 16
Joined: Fri May 30, 2008 6:47 am

Return to Reporting

Who is online

Users browsing this forum: No registered users and 3 guests