Format Dates

[lonnieCross]

I'm trying to format dates in the select statement:

CONVERT(VARCHAR(10),GETDATE(),110) returns an error. How should this be handled?

Thanks!

[agershun]

1. Unfortunately, the CONVERT() and CAST() functions are not realized yet. I will include them in version 0.0.36 on January 9, 2014

By default Alasql keeps all dates in string format like YYYY.MM.DD HH:MM:SS, so I will prepare conversion number 110 in next realization.

[agershun]

1. I fixed CONVERT() function.

You can see the example of different CONVERT styles [in jsFiddle((http://jsfiddle.net/agershun/qytn1n0L/) or in test212.js:

1. BTW Use GETDATE() instead GETDATE.(see another example)

Does this solve the problem?

[agershun]

Another options:

1. You can use Moment.js and Numbers.js libraries togeather with Alasql to format dates and numbers (see examples in Wiki: dates and numbers).
2. You can use sprintf() for other type of formatting.
3. You can write formatting function yourself:

    alasql.fn.fmt = function(n) {return ((n/100)|0) + '.'+ (n%100)}
    var res = alasql(SELECT fmt(RANDOM(10000)) FROM RANGE(1,10)');

See the example in jsFiddle.

[vivekratnavel]

CONVERT() function does not work with group by clause.

SELECT count(*), CONVERT(STRING,DATE(FIRST(date)),1) FROM table group by MONTH(date), DAY(date)

I am getting this as output:
{CONVERT(STRING,DATE(FIRST(date)),1): "aN/aN/aN", COUNT(*): 332}

[mathiasrw]

@vivekratnavel Thanks for letting us know - Ill create an issue with it...

[mathiasrw]

@vivekratnavel I would love if you could share the table and some demo data that creates the problem

[vivekratnavel]

You can use the javascript in this fiddle to insert values into the table.
http://jsfiddle.net/vivekratnavel/31n7f2qz/

Jsfiddle becomes slow when the table is loaded with 200000+ rows. My advice is to use the script locally.

[agershun]

This is not a problem of CONVERT but the statement:

    SELECT count(*), CONVERT(STRING,DATE(FIRST(date)),1) FROM table group by MONTH(date), DAY(date)

Here AlaSQL does not know where to take date variable, because there is no such data in GROUP BY clause.