Re: FrontBase SQL: date formatting, „local variable“?
Re: FrontBase SQL: date formatting, „local variable“?
- Subject: Re: FrontBase SQL: date formatting, „local variable“?
- From: Chuck Hill <email@hidden>
- Date: Fri, 11 Mar 2016 20:59:53 +0000
- Thread-topic: FrontBase SQL: date formatting, „local variable“?
It is supposed to be SQL92 compliant. If standard SQL can’t do it, you might be out of luck. This is not something you can do client-side?
Chuck
On 2016-03-11, 12:47 PM, "webobjects-dev-bounces+chill=email@hidden on behalf of OC" <webobjects-dev-bounces+chill=email@hidden on behalf of email@hidden> wrote:
>Hello there,
>
>any FrontBase SQL gurus on-line? For a derived attribute, I would need
>
>-- (a) to format a TIMESTAMP as “%Y%n%e”
>
>So far the best I have found is
>
>CAST(EXTRACT(year FROM DATE) AS VARCHAR(4)) ||
>CAST(EXTRACT(month FROM DATE) AS VARCHAR(2)) ||
>CAST(EXTRACT(day FROM DATE) AS VARCHAR(2))
>
>but this does not zero-pad, ie., instead of the desired “20160302” I get improper result “201632”.
>
>How to zero-pad the month and day?
>
>-- (b) to define and use a “local variable”
>
>The problem is that the “DATE” above is not a column, but a SELECT sub-expression. If I repeat it again and again and again, it does work all right, but the SQL is really ugly (and, I suppose, also inefficient). I would need something like
>
>VAR mydate=( SELECT C_START_DATE FROM T_TIME_CHUNK WHERE T_TIME_CHUNK.C_UID = C_TIME_CHUNK_UID )
>CASE
> WHEN mydate IS NULL THEN 'fallback-value'
> ELSE
> CAST(EXTRACT(year FROM mydate) AS VARCHAR(4)) ||
> CAST(EXTRACT(month FROM mydate) AS VARCHAR(2)) ||
> ...
>END
>
>Can this be done anyhow?
>
>Thanks a big lot,
>OC
>
>
> _______________________________________________
>Do not post admin requests to the list. They will be ignored.
>Webobjects-dev mailing list (email@hidden)
>Help/Unsubscribe/Update your Subscription:
>
>This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden