QB equivalent for STRING(DATEFORMAT(...

Classic Servoy
1

Hi

I have a SELECT statement which looks (in part) like the following and I am looking for the QB equivalent:

SELECT\
    STRING(DATEFORMAT(MIN(pStart.from_date), 'yyyy-mm-dd' )),\

I tried following but although i gives no error it does not seem to be correct.

query.result
    .add(query.functions.cast((pStart.from_date.min), 'yyyy-mm-dd'), QUERY_COLUMN_TYPES.TYPE_STRING)

Any hint how the QB equivalent might look like?

Thanks and regards,

2

Hi Robert,

I don’t think the cast will work. It only takes two arguments: the value and the type.
I believe that the more extensive functions for (date) formatting are all vendor-specific (i.e. convert() or to_char()), so it would not be supported in the QB API.

You should be able to build the date format from scratch, but you’ll have to take each part of the date and also pad it with zeros for months and days that would be single-digit.
Something like:

var month = q.result.add(q.functions.concat('0',q.columns.orderdate.min.month.cast(QUERY_COLUMN_TYPES.TYPE_STRING)).substring(0,3))

That would be just one part, so would ```
concat(concat(month, ‘/’),concat(‘/’,year))…


But it really should be quite easy. Reply back if you have any troubles.
3

Thanks Sean!