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? :)
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
