Solved

# AS400 DAYOFWEEK(GENERAL DATE)

Posted on 2015-02-05
445 Views
Hi,

I want to use the DAYOFWEEK Function in an AS400 SQL call.  The date is currently  = 42041 (2/6/2015 or 20150206).

How can I use the DAYOFWEEK Function with this date format?

Otherwise how do I convert 42041 to 20150206?

Also is my terminology for 42041 (General Date) correct?  What should it be?

Thanks
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 4
• 3

LVL 18

Expert Comment

ID: 40592591
How does 2015-02-06 translate to 42041 ?
0

Author Comment

ID: 40592603
OBRGDT = 20150206

DAYS(DATE(SUBSTR(OBRGDT,1,4)||'-'||SUBSTR(OBRGDT,5,2)||'-'||SUBSTR(OBRGDT,7,2))) AS OBRGDT_1

You may ask why I don't just use OBRGDT but it is too long a story (presenting data in Excel).

My other option is to convert 42041 to 20150206 if you know how to do that?
0

Author Comment

ID: 40592605

CASE WHEN OBRGDT = 0 THEN 0  ELSE DAYS(DATE(SUBSTR(OBRGDT,1,4)||'-'||SUBSTR(OBRGDT,5,2)||'-'||SUBSTR(OBRGDT,7,2))) - DAYS('1/1/1900') + 2 END
0

LVL 48

Expert Comment

ID: 40592612
As you refer to DAYOFWEEK I'm assuming you are using DB2 so I'd like to propose this article for reading

DB2 Basics: Fun with Dates and Times

It has numerous examples of how date/times and related functions work in DB2,  including  current date

but I don't understand "The date is currently  = 42041"
0

Author Comment

ID: 40592632
If you open excel, type '=TODAY()', press enter then format the cell as 'General' you'll get 42041.

I need the date in this format for formatting purposes in Excel.  I just need to be able to either convet 42041 to 20150206 or use DAYOFWEEK(42041).

I looked at the site you showed me and no luck.  Still looking
0

LVL 48

Expert Comment

ID: 40592659
I'm confused.
Are you performing this calculation in DB2 (where you can use DAYOFWEEK)
or in Excel (where you can use WEEKDAY)
?

e.g. In Excel
=WEEKDAY(TODAY())

and
Excel will accept dates (e.g. 31/12/1900 or 12/31/1900 etc.) depending on your locale settings

If in DB2 you would pass a timestamp field into the function

SELECT DAYOFWEEK(current date)  FROM sysibm.sysdummy1

{+edit, a correction; incorrect use of weekday in line above}
0

LVL 18

Accepted Solution

Dave Ford earned 300 total points
ID: 40592661
Thank you for the learning experience. I never knew you could represent a DATE like that.

This works for me:

``````select date(42039 + days('1900-01-01')),
dayOfWeek(date(42039 + days('1900-01-01')))
from sysibm.sysdummy1

DATE            DAYOFWEEK
2015-02-06              6
``````

HTH,
DaveSlash
0

LVL 48

Assisted Solution

PortletPaul earned 200 total points
ID: 40592664
oh and "day zero" in Excel is 1900-12-31, add 42041 days to that and you arrive at 2015-02-06

i.e. enter 0 into cell now format that cell as a date in another cell add 42041 to that 0
0

LVL 48

Expert Comment

ID: 40592667
Excellent work! (learning experience for me also)

Cheers.

sorry for the cross posting, can be a bit confusing
0

LVL 18

Expert Comment

ID: 40594952
0

Author Comment

ID: 40595357
Hi deveslash.

select date(42039 + days('1900-01-01')),
dayOfWeek(date(42039 + days('1900-01-01')))
from sysibm.sysdummy1

works for me.

Thanks and thanks to PortletPaul.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm nā¦
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people unaā¦
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scannerā¦
Why Skyport?
###### Suggested Courses
Course of the Month5 days, 11 hours left to enroll