'SELECT YEAR(NOW())' works on Chrome but not on Firefox or Safari.

[report-zone]

As the title says. Easy to reproduce using 0.6.5 in codepen no data necessary

returns 2021 in chrome and nothing in firefox.

[report-zone]

Additionally I found that

SELECT CONVERT(STRING,NOW(),1)

Chrome -> 02/01/21
Firefox -> aN/aN/aN

[report-zone]

Same issue on Safari for both

Both work on Version 88.0.705.56 of MS Edge

[report-zone]

A little bit of digging :-) NOW() returns a non standard format, which chrome, safari and edge (V8 engine) can handle, but Firefox and Safari cannot.

stdfn.NOW = function () {
	var d = new Date();
	var s =
		d.getFullYear() +
		'.' +
		('0' + (d.getMonth() + 1)).substr(-2) +
		'.' +
		('0' + d.getDate()).substr(-2);
	s +=
		' ' +
		('0' + d.getHours()).substr(-2) +
		':' +
		('0' + d.getMinutes()).substr(-2) +
		':' +
		('0' + d.getSeconds()).substr(-2);
	s += '.' + ('00' + d.getMilliseconds()).substr(-3);
	return s;
};

This will work on most browser if not all of them.

// ISO format by default => 2021-02-02T04:09:13.263Z
stdfn.NOW = function () {
	var d = new Date();
	return d.toISOString();
};

// locale date format if needed.     => 2021-02-01
stdfn.DATE_FORMAT_LOCALE= function (d) {
  return new Date(d).toLocaleDateString();
};

// current custom format => 2021.02.01 20:08:37.181
stdfn.DATE_FORMAT_ALASQL = function (d) {
  var d = new Date(d);
  var s =
    d.getFullYear() +
    '.' +
    ('0' + (d.getMonth() + 1)).substr(-2) +
    '.' +
    ('0' + d.getDate()).substr(-2);
  s +=
    ' ' +
    ('0' + d.getHours()).substr(-2) +
    ':' +
    ('0' + d.getMinutes()).substr(-2) +
    ':' +
    ('0' + d.getSeconds()).substr(-2);
  s += '.' + ('00' + d.getMilliseconds()).substr(-3);
  return s;
};

Now SELECT YEAR(NOW()) will return the current year this allow for queries that will always work
like SELECT * from some_table where some_date >= (YEAR(NOW()) -2) i.e. everything up to 3 years from current date.

[mathiasrw]

Thank you so much for looking into this!

Changing date format is not a small change for all the implementations out there. The format is replicating how MySQL does it as I recall - but we really need to get firefox and safari working.

I will not be able to look at this this week, but hope in the weekend.

[report-zone]

Sticking with a more standard javascript format and providing custom formatters for anything else might be a better way to got and also easier to maintain.

My main concern is to have it woking for most (better all) browsers, though I fear going with the MySQL format might make the implementation much more complex and potential become a performance bottleneck :-)

[report-zone]

Any progress on this ?

[mathiasrw]

@report-zone No progress. PRs welcome.

[mathiasrw]

Released as part of https://github.com/AlaSQL/alasql/releases/tag/v2.0.1