Re: An easy (I think) SQL question

On 29-Jun-2016 12:06 -0500, Charles Wilt wrote:

On Wed, Jun 29, 2016 at 12:45 PM, CRPence wrote:

On 29-Jun-2016 10:26 -0500, Rob Berendt wrote:

Using the CURRENT_DATE - (DAY(CURRENT_DATE) - 1) days - 3 months
works

But I usually use
last_day(current_date) + 1 day - 4 months

It'd be nice if IBM added a FIRST_DAY

While I agree, for the ease of having the built-in already there
and generally available, there is little point lamenting the lack
of something so easily done oneself:

create function first_day
( inpDate date
) returns date
language sql
deterministic
return
insert( char( inpDate, ISO ), 9, 2, '01' )
;

Think you're missing a cast back to DATE...unless it'd be
implicit...

Implicit. As tested on an IBM i 7.1 system, the above function worked swimmingly.

FWiW: While I am aware that such implicit casting has failed for similar, at least on some past releases, what is shown should effect without error, an implicit cast from the VARCHAR result of the INSERT expression, cast into the DATE data type per the RETURNS DATE specification. To avoid any dependence on that implicit casting, and thus both to avoid any current or possible future defects and to avoid a lack of support on any prior release where implicit cast is not available [though I expect then, that the INSERT scalar is also unavailable], per a possible failure by the SQL to effect that casting implicitly, then the explicit wrapping of that expression in the DATE casting scalar may have some value -- I rather prefer the succinctness of the omission :-)

Regards, Chuck