Syntax questions: now() or now(*), today() or today(*)?

[Former Member]

For SQLAnywhere v5+

Syntax questions: now() or now(*), today() or today(*)? Both seem to work.

However count() does not work. It requires the asterisk.

So what versions of Now and Today are more ANSI correct and more compatible? Does it really matter which one I use?

Note: For some reason, the forum editor remove my asterisks. It's supposed to be: now() or now(asterisk), and today() or today(asterisk)

[MarkCulp]

FWIW Your asterisks were not seen because of the way that the Markdown processor works. I have escaped the '*' to have them show up.

[Former Member]

Thanks, Mark

[MarkCulp]

ANSI does not define the "now" or "today" date functions. Instead it defines CURRENT_TIMESTAMP (and CURRENT_DATE, CURRENT_TIME) builtin functions.

SQL Anywhere includes the now(), today(), CURRENT TIMESTAMP, CURRENT DATE, CURRENT TIME and many other date/time related builtin functions. Many of these builtins are included for compatibility with other RDBMSes.

So it doesn't really matter if you use now(*) or now() or .... etc. since none of them are ANSI and there is no "right answer" that will be compatible with all of the major database systems. I would pick one that you are comfortable with and use it.

[Former Member]

That's great! Thanks for the answer, Mark. I guess I'll just keep using it the way I've been.

[VolkerBarth]

In addition to Mark's answer, I would suggest to use NOW() and TODAY() without the asterik - from a formal/logical point of view:

Returning the current date/time/timestamp is by definition a NILADIC function, i.e. it needs no argument, thus its argument list is empty - commonly expressed by an empty bracket. AFAIK, this convention is used in most programming languages as well.

---

In contrast, the COUNT(*) aggregate does take an argument, namely an expression to count. Specifying the asterik is just a way to say "all rows". Therefore IMHO it seems reasonable to use it with (*) - although newer SQL Anywhere versions (at least 10.0.1 and above) seem to accept COUNT() as well - and for v12, it's documented:

Return the number of rows in each group. COUNT(*) and COUNT() are semantically equivalent.

[Former Member]

Very interesting take on this. I fully agree with your position, and am using Count(*), but Now(), and Today(), as that seems to make more sense to a programmer like me.