Re: Showcase Date Handling

On 16-Sep-2011 10:43 , Michael Ryan wrote:

Anyone know how I would do this from Showcase:

GROUP BY
CASE
WHEN MONTH ( DATE ( MYDATE, YYYYMMDD ) )>= 10
THEN YEAR ( DATE ( MYDATE, YYYYMMDD ) ) + 1
ELSE YEAR ( DATE ( MYDATE, YYYYMMDD ) )
END

in standard SQL? MYDATE is an 8.0 numeric field containing a date
like 20110916.

Being numeric data in the desirable YYYYMMDD representation, I do not see any requirement to use either DATE casting or the CASE expression in order to achieve the desired result. Various simple arithmetic expressions enable easily incrementing the year portion of the decimal value representing the date, which can then be cast in order to both specify data type and effect integer truncation. Note: rounding for decimal and integer data types on numeric assignments [excluding decfloat data type] must be requested using the ROUND() scalar.

The four expressions included below should effect the desired YEAR-like result; the later SQL shows the last of those expressions on both the SELECT and the GROUP BY clause:

int( (mydate+9000) / 10000 )
dec( (mydate+9000) / 10000 , 4)
dec( mydate/10000 + .9, 4)
int( mydate/10000 + .9 )

select ... int( mydate/10000 + .9 )
...
group by int( mydate/10000 + .9 )

Regards, Chuck