Query Builder support for DATEPART

[SteveInLA]

I see that QBFunction supports extracting month, day, year, hour, minute and second but can it also support the rest of the T-SQL DATEPART components? Specifically I am looking for quarter and dayofyear but week, weekday and millisecond would also be handy. See this link for reference.

[paronne]

Hi Steve,

QBFunction indeed doesn't support the other DATEPART components since they are not supported by the engine the QBuilder is build on.
To perform such type of queries using the query builder you can create a custom function of your own in your SQL server, which would return the DATEPART.

For instance for a weekday in SQL Server it could look like

Code: Select all

CREATE FUNCTION dbo.myweekday (@input datetime)
RETURNS int
AS BEGIN
    RETURN datepart(weekday, @Input)
END

Code: Select all

select dbo.myweekday(current_timestamp)
==> returns 6 (Friday)

then in the QueryBuilder you would use

Code: Select all

q.result.add(q.functions.custom('dbo.myweekday',q.columns.startdatetime));

please take into account that such function needs to be included in all you DBs, development DB, testing DB, production DB etc..

Regards,
Paolo

[SteveInLA]

Is there a way to apply a custom function like this to a query based on an in memory datasource?

[SteveInLA]

Does this only work in 8.4? I am using 8.3.3 and there is code completion for query.functions.custom(name, args...) but when I try it it gives this error:

Code: Select all

ERROR com.servoy.j2db.util.Debug - Not a valid sql name: dbo.datepart_year

This error occurs executing this code:

Code: Select all

application.output(databaseManager.getSQL(query));

The functions do appear in the database's Procedures folder.

[paronne]

Hi Steve,

the query builder function query.functions.custom(name, args...) is supported also in 8.3.3 and previous versions. This function allow to run any custom function available in the DB within the Query Builder.
Not sure why you get the error, maybe a typo; have you tried to test the function directly within your database (using any database tool) ?

About in-memory datasources, that is a very interesting question, honestly i don't know if is possible at all.. i would try at first to test if i can run any of the built in function of HyperSQL as a custom function (http://hsqldb.org/doc/guide/builtinfunctions-chapt.html)
If the built in functions can't be used to obtain the datepart, i don't know if is possible to apply the same solution as for SQL Server; since the in-memory DB is created dynamically using UUID as Table and Column names, i am not sure if you can alter at all the DB using rawSQL.. there is surely a challenge to map the table/column names to the proper UUIDs.

Maybe one of our R&D experts can tell something more about it.

Regards,
Paolo

[SteveInLA]

No typos. Functions work in SQL Server Management Studio using 'SELECT dbo.datepart_dayofyear(o.orderdate) FROM orders as o'. What is odd is that this method works fine from within Servoy:

Code: Select all

var q = 'SELECT dbo.datepart_dayofyear(o.orderdate) FROM orders as o'
var ds = databaseManager.getDataSetByQuery('servoy_test_mssql',q,null,10);

I have submitted a case here: https://support.servoy.com/browse/SVY-13196 with sample solutions.
I was able to get this to work in PostgreSQL using query builder but had to exclude the schema and just send the name of the function, but when I do that in MS SQL, I get a different error.
Thanks for looking into this,
Steve