Visual Query Builder--Extract Date field as yyyy-mm

[cowannbell]

How within Visual Query builder, can I export a date column from yyyy-dd-mm to yyyy-MM?  

I have tried several things but keep getting an error.

[abacon]

@cowannbell In Alteryx, I would pull the date in with the yyyy-dd-mm format, and use a datetime tool to convert the data. The visual query builder can be funky.

See below screenshot.

Bacon

[cowannbell]

I need to change the format before pulling the data from the source.  I have to do it in Visual Query Builder.

[abacon]

@cowannbell What is the need for doing it in the query builder? You can use the below function to accomplish this, does that work for you?

Bacon

Format(CAST(datefield AS DATE), 'yyyy-MM') 

[cowannbell]

I still get an error.  I had tried Format before but not the cast but I get the same error.

FORMAT(Cast("DENTAL_CLAIM"."SRV_START_DT" as "DATE"), 'yyyy-MM'),  This is what I input

"FORMAT"(Cast("DENTAL_CLAIM"."SRV_START_DT" as "DATE"), 'yyyy-MM'), This is what it converts it to.  It seems to not realize that FORMAT is the function.

The error is 

Error: Input Data (1): Error SQLPrepare: [IBM][CLI Driver][DB2/AIX64] SQL0440N No authorized routine named "FORMAT" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

As for why I need it in Visual Query, is because I'm using a Dynamic Input tool.  I pull date from table source and take that field and attach it to the dynamic input tool to update the data from query from the date field, so as not to have to bring back all of the records and then filter them out, as the source is very large.

[cowannbell · Accepted solution]

I figured it out.  I had to use To_Char