Re: AW: rpg Embedded SQL: date/week order by

On Feb 21, 2016, at 12:03 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

Adding 19000000 and concatenating the result with '000000', converts the
numeric date into a valid character representation of a timestamp, which can
be used in SQL where ever a date is needed.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Michael
Schutte
Gesendet: Sunday, 21.2 2016 17:21
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: rpg Embedded SQL: date/week order by

It appears xdte isn't a true date field. Cyymmdd numeric field.



Sent from my iPhone

On Feb 21, 2016, at 4:29 AM, Peter Dow <petercdow@xxxxxxxxx> wrote:

Hi Booth,

Maybe this?

'select'
+ 'Year(XDTE),'
+ ' Week(Dec((19000000 + XDTE), 8, 0) concat ''000000''),'
+ ' XLOC, XAQT, XORD, XQTY'
+ ' from FILEA'
+ ' join FILEB on XITM = XITM2 and XDTE = XDTE2'
+ ' where XITM = ?'
+ ' group by Year(XDTE), Week(Dec((19000000 + XDTE), 8, 0)'
+ ' concat ''000000'')'
+ ' order by XDTE desc';

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

On 2/20/2016 1:51 PM, Booth Martin wrote:
Here is the prepared statement I would like to use. It does not compile.

It will compile & run if I order by week, but that gives me the issue of
this thread.

'select'
+ ' Week(Dec((19000000 + XDTE), 8, 0) concat ''000000''),'
+ ' XLOC, XAQT, XORD, XQTY'
+ ' from FILEA'
+ ' join FILEB on XITM = XITM2 and XDTE = XDTE2'
+ ' where XITM = ?'
+ ' group by Week(Dec((19000000 + XDTE), 8, 0)'
+ ' concat ''000000'')'
+ ' order by XDTE desc';

On 2/20/2016 3:31 PM, Alan Shore wrote:
I am not too sure what poibt you are trying to maje but i belive you
have to use the year of the date as well Maybe if you posted the sql
it might help

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related

questions.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.