Complex date formatting + concat in SQLServer, how to translate it to Jooq?

[Adriana Gomez]

Hi,

I have this query I need to translate to Jooq. It's written in SQLServer.

SELECT 

        CONVERT(datetime, CONVERT(nvarchar(20), YEAR(gv.creation_date)) + RIGHT('0' + CONVERT(nvarchar(20), MONTH(gv.creation_date)), 2) + '01') AS 'month'

FROM TABLE gv

CREATION_DATE in table is a datetime field.

As I understood, this query wants to return the date formatted with yyyyMM01, assuming for each month the day 01. Notice that the month must have 2 digits.

I tried to do this in Jooq, because I need that my application runs in MySQL and Oracle. No idea how to proceed. I did something like:

        Field<String> year = DSL.year(CaseMappingSql.CREATION_DATE).cast(String.class);

        Field<String> month = DSL.lpad(DSL.month(CaseMappingSql.CREATION_DATE).cast(String.class), 2, "0");

        Field<String> day = inline("01");

        Field<String> concatDate = DSL.concat(year, month, day);

        String sql = dsl(). //

                select(inline(Convert.convert( //

                                concatDate, //

                                Timestamp.class))). //

                from(CaseMappingSql.TABLE). //

                getSQL();

This looks terrible! And I get an error about CannotCastException.

Any ideas? :)

[Lukas Eder]

Hello,

I think that the best way for you to implement this is by implementing a CustomField:

- http://www.jooq.org/javadoc/latest/org/jooq/impl/CustomField.html

- http://www.jooq.org/doc/3.1/manual/sql-building/queryparts/custom-queryparts/

Your CustomField would then take care of:

- Properly distinguishing SQL Server, MySQL and Oracle

- Properly render the SQL string and bind variables

A stub implementation:

import static org.jooq.SQLDialect.SQLSERVER;

import static org.jooq.SQLDialect.SQLSERVER;

import java.sql.Timestamp;

import java.util.Date;

import org.jooq.BindContext;

import org.jooq.Field;

import org.jooq.QueryPart;

import org.jooq.RenderContext;

import org.jooq.SQLDialect;

import org.jooq.exception.DataAccessException;

public class FirstOfMonth<T extends Date> extends CustomField<T> {

    private Field<T> date;

    protected FirstOfMonth(Field<T> date) {

        super("firstofmonth", date.getDataType());

        this.date = date;

    }

    @Override

    public void toSQL(RenderContext context) {

       context.sql(delegate(context.configuration().dialect()));

    }

    @Override

    public void bind(BindContext context) throws DataAccessException {

        context.bind(delegate(context.configuration().dialect()));

    }

    private QueryPart delegate(SQLDialect dialect) {

        if (dialect.family() == SQLSERVER) {

            return DSL.queryPart("convert(datetime, convert(nvarchar(20), year({0})) + right('0' + convert(nvarchar(20), month({0})), 2) + '01')", date);

        }

        else {

            throw new UnsupportedOperationException();

        }

    }

}

As your custom function has a very specific purpose, I'd opt for such a solution, rather than playing around with various jOOQ DSL functions per dialect. Once this works, you can add other "translations" of the same for other dialects in the delegate() method. An example call:

System.out.println(DSL.using(SQLSERVER)

                      .render(new FirstOfMonth(creation_date)));

Note, I'd recommend not resorting to string operations, but use date time functions instead. Another SQL Server example:

DATEADD(DD, 1 - DAY(GETDATE()), CAST(GETDATE() AS DATE))

Hope this helps. I think the community would probably like to see the final solution that you came up with. If you want, feel free to post your code to this list...

Cheers

Lukas

2013/9/6 Adriana Gomez <adri...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Adriana Gomez]

Hi,

Thanks for the reply. I'm trying with your suggestion, but when I do:

return DSL.queryPart("convert(datetime, convert(nvarchar(20), year({0}).....

I get a compilation error :

The method queryPart(String, Field<T>) is undefined for the type DSL. It seems in DSL this queryPart is not defined...

[Adriana Gomez]

Or should I do:

return DSL.field("convert(datetime, convert(nvarc.......................

??

[Lukas Eder]

Hello,

My bad. DSL.queryPart() methods are going to be added in jOOQ 3.2, which has not yet been released:

https://github.com/jOOQ/jOOQ/issues/2630

Yes, you can resort to DSL.field(), which will work the same way in your case.

2013/9/6 Adriana Gomez <adri...@gmail.com>

Or should I do:

return DSL.field("convert(datetime, convert(nvarc.......................

??

--

[Adriana Gomez]

Hi,

OK, since I didn't like that query, it was something too complex to just get the objects per month, I changed the query to have something like:

                select(DSL.count().as("nr"), //

                        TYPE.as("case_type"), //

                        year(CREATION_DATE).as("year"), //

                        month(CREATION_DATE).as("month")). //

                from(TABLE). //

                where(CREATION_DATE.greaterOrEqual(inline(fromDate))). //

                and(CREATION_DATE.lessThan(inline(untilDate))). //

                groupBy(TYPE, //

                        year(CaseMappingSql.CREATION_DATE), //

                        month(CaseMappingSql.CREATION_DATE)).getSQL();

                        

With this change, it was more than enough to get the count per month. Then, in my rowmapping, I could create the date with the returned month and year and using the day 01 by default.

I'm not sure if this is the best solution, but at least the query is much more simple and it works :)

[Lukas Eder]

Great. Good to know it worked out, and thanks for the feedback

Cheers

Lukas

2013/9/17 Adriana Gomez <adri...@gmail.com>

--