Re: SQL dates and numeric columns

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