Re: SQL: how to retrieve current date in YYYYMMDD format

On 08 Aug 2012 09:56, Stone, Joel wrote:

The following doesn't zero-fill the MM and DD fields. Is it possible
to get SQL to zero-fill these values for months 1 thru 9 and also
days 1 thru 9?

YEAR(curDate()) || MONTH(curDate()) || DAY(curDate())

Purpose: I am trying to load the current date (20120808) into an old
column defined as YYYYMMDD alpha.

My preference for that had long been to use the CURRENT DATE special register instead of the CURDATE function, to cast the date value to the effective desired date-string format, and to then remove the separators:

replace(char(current_date, ISO), '-', '')

However the VARCHAR_FORMAT built-in scalar function eventually came along, and that is more succinct and obvious although requiring a timestamp instead of a date expression [optionally using NOW() to replace current timestamp]:

varchar_format(CURRENT_TIMESTAMP, 'YYYYMMDD')

To get the given expression to function correctly, the [implicit] cast to character must be changed to a cast to character that preserves the leading zero of a two-digit numeric; e.g. the DIGITS function. Because all of YEAR, MONTH, and DAY return large integer, that would require also first casting those to something like DEC(2); not very pretty IMO:

digits(dec( YEAR(curDate()),2))
|| digits(dec(MONTH(curDate()),2))
|| digits(dec( DAY(curDate()),2))

Regards, Chuck