Newsletters Subscriptions Forums Media Kit About Us Contact Search Home

OS/400 Edition
Volume 3, Number 72 -- October 24, 2003

Query/400 Determines the Day of Week

Hey, Ted:

Here is another example of date and time formats in Query/400.

These are the result fields I use to make Query display the day of the week.

               Define Result Fields

Field       Expression                         Len   Dec
----------  ---------------------------------  ----  ----
Y           substr(digits(idate), 1, 4)

M           substr(digits(idate), 5, 2)

D           substr(digits(idate), 7, 2)

F_DATE      date(d||'.'||m||'.'||y)

ANYSUNDAY   date('01.01.1950')

DIF_DAYS    days(f_date) - days(anysunday)       9     0

NBR_WK      dif_days/7                           8     2

F_WEEK_DAY  microsecond('2001-01-01-01.01.01.
            '||substr(digits(nbr_wk), 1, 6))
             * 7
WEEK        'MoTuWeThFrSaSu'

DAYNBR      dif_days - f_week_day

WEEK_DAY    substr(week, ( dif_days -
            f_week_day - 1) *2 + 1 , 2)


Thanks for the example, Harald. I admire your ingenuity.

In this example, IDATE is an eight-digit numeric field in YYYYMMDD format. For the benefit of the interested reader, here is an explanation of the result fields:

Y the year portion of IDATE
M the month portion of IDATE
D the day portion of IDATE
F_DATE IDATE converted to the date data type
ANYSUNDAY the date of a Sunday, needed as a base for the day-of-week calculation
DIF_DAYS the number of days between ANYSUNDAY and IDATE
NBR_WK the number of weeks between ANYSUNDAY and IDATE
F_WEEK_DAY the number of days in whole weeks between ANYSUNDAY and IDATE
WEEK a string of day abbreviations
DAY_NBR the number of days remaining after removing whole weeks from the number of days between IDATE and ANYSUNDAY
WEEK_DAY the abbreviation of the day of the week on which IDATE falls


Sponsored By

RPGsp users don't settle for less!

Why settle for a tool that's just an application builder, or just a server, or just a designer, or just a green-screen converter?

You need an integrated Web development tool that can do all of the above. And you need the best!

RPGsp has what you need. Don't settle for less.

Free trial and information at


Advanced Systems Concepts
Profound Logic Software



Embedding Markup in XML

Query/400 Determines the Day of Week

Reader Feedback and Insights: He Likes Common Table Expressions

Howard Arner
Joe Hertvik
Ted Holt
David Morris

Managing Editor
Shannon Pastore

Publisher and
Advertising Director:

Jenny Thomas

Advertising Sales Representative
Kim Reed

Contact the Editors
Do you have a gripe, inside dope or an opinion?
Email the editors:

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.