QB equivalent for STRING(DATEFORMAT(...

Questions and answers regarding general SQL and backend databases

QB equivalent for STRING(DATEFORMAT(...

Postby huber » Mon Feb 05, 2018 12:51 pm

Hi

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

Code: Select all
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.
Code: Select all
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,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Re: QB equivalent for STRING(DATEFORMAT(...

Postby sean » Mon Feb 05, 2018 7:24 pm

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:
Code: Select all
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
Code: Select all
concat(concat(month, '/'),concat('/',year))...
that with each date part and the separator.

But it really should be quite easy. Reply back if you have any troubles.
Software Engineer
Servoy USA
sean
 
Posts: 370
Joined: Mon May 21, 2007 6:26 pm
Location: USA

Re: QB equivalent for STRING(DATEFORMAT(...

Postby huber » Tue Feb 06, 2018 3:51 pm

Thanks Sean!
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 5 guests

cron