×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
