Re: SQL dates and numeric columns

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Dave,

Just fyi, CONCAT is both a function (as you used it), and an operator, e.g.

date( varchar(Y) concat '-' concat varchar(M) concat '-' concat varchar(D) )

Better than using it as a function, but not as simple as your preferred 
'+'.  But as Birgitta mentioned, better than using ||, which will cause 
issues in an international environment.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx
pdow@xxxxxxxxxxxxxx /

On 9/5/2023 7:42 AM, Dave wrote:
> I'm surprised it's still so difficult to get a date from a numeric value
> Concat gives me this horrendous code :
>
> date(
>
> concat(
>
>   concat(
>
>    concat(trim(char(Y)), '-'), concat(trim(char(M)), '-')
>
>         )
>
>       , trim(char(D)))) " MyDate  "
>
> I would have liked to have been able to use
> Y* 10000 + M* 100 + J
>
>
>
> ---------- message ---------
> From: Dave <>
> Date: Fri, 1 Sept 2023 at 10:40
> Subject: SQL dates and numeric columns
> To: Midrange Systems Technical Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
>
>
> Hi,
>
>
>
> Trying to do date operations on a physical file where the date is coded in
> 3 different numeric fields Y, M and D
>
>
>
> 1 -  I created a view, can I do better than this :
>
> date(trim(char(Y))!!'-'!!trim(char(M))!!'-'!!trim(char(D))) "MyDate"
>
>   ?
>
>
> 2 – This works in an SQL session but the RPG compiler doesn’t seem to like
> the ‘ !’ character. Any ideas ?
>
>
>
> Thanks