🚀 go-pugleaf

On Mon, 11 Jan 1999 11:12:55 +0800, "Geoff S. Wass" <gswass@ibm.net>
wrote:

>This results in YYYYMMDD, except that single digit MMs or DDs have no
>leading zero (so, for example, 9 December 1998 comes out as 1998129 and
>not 19981209). There is a way to do that, too, but I don't have an SQL
>reference at hand.
>
In thread 'Date' Steve Koterski replied on 15-12-98 as quoted below.

Ivar B. Jessen

>SELECT CAST(EXTRACT(YEAR FROM Data) AS CHAR(4)) ||
>  SUBSTRING(CAST((EXTRACT(MONTH FROM Data) + 100) AS CHAR(3)) FROM 2 FOR 2)

>  ||
>  SUBSTRING(CAST((EXTRACT(DAY FROM Data) + 100) AS CHAR(3)) FROM 2 FOR 2) 
>  || Name
>FROM "cont1998.dbf"

>Note that there is a little extra manipulation in here. Specifically,
>adding 100 to the extracted MONTH and DAY values and use of the SUBSTRING
>function. This is because of the way local SQL handles single-digit month
>and day values: it does not left-pad the values with a zero. Because of
>this, 1/20/1998 would come out as 1998120. By adding 100 to the extracted
>MONTH and DAY values and then retrieving the last two digits with
>SUBSTRING, that date would instead be translated as 19980120. This is more
>in keeping with the format of dates after using the dBASE function DTOS.