FrontBase SQL: date formatting, „local variable“?
FrontBase SQL: date formatting, „local variable“?
- Subject: FrontBase SQL: date formatting, „local variable“?
- From: OC <email@hidden>
- Date: Fri, 11 Mar 2016 21:47:15 +0100
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