Re: [EXTERNAL] Date conversion

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

One of the IBMers gave a presentation at the recent COMMON on using this 
- called it Speed Dating!

Vern

On 5/4/2023 8:20 AM, Greg Wilburn wrote:
> For DB2 Web Query, IBM recommended a join to another table to do this... I have a file called DATE_CONV.  I believe they supplied it with Web Query.
>
> You simply join to it using your date (in your format), then you have access to the date in virtually any format you'd like.  You can update the Holiday information yourself... I've found it very useful for many other things.
>
> Field             Text                                          Len  Dec
> DC_DATE           Date (date format)                             10   L
> DC_JDE_JUL        JDE Julian Date (CYYDDD decimal)                6    0
> DC_MDYY_P         Date (MMDDYYYY packed decimal)                  8    0
> DC_MDYY_Z         Date (MMDDYYYY zoned decimal)                   8    0
> DC_MDYY_C         Date (MMDDYYYY character)                       8
> DC_YYMD_P         Date (YYYYMMDD packed decimal)                  8    0
> DC_YYMD_Z         Date (YYYYMMDD zoned decimal)                   8    0
> DC_YYMD_C         Date (YYYYMMDD character)                       8
> DC_MDY_P          Date (MMDDYY packed decimal)                    6    0
> DC_MDY_Z          Date (MMDDYY zoned decimal)                     6    0
> DC_MDY_C          Date (MMDDYY character)                         6
> DC_YMD_P          Date (YYMMDD packed decimal)                    6    0
> DC_YMD_Z          Date (YYMMDD zoned decimal)                     6    0
> DC_YMD_C          Date (YYMMDD character)                         6
> DC_CC_CHAR        Century (2 characters)                          2
> DC_YY_CHAR        Year (2 characters)                             2
> DC_MM_CHAR        Month (2 characters)                            2
> DC_DD_CHAR        Day (2 characters)                              2
> DC_YEAR           Year (4 digits)                                 9    0
> DC_DOW            Day of week (1-7)                               9    0
> DC_DOW_ISO        Day of week (1-7)                               9    0
> DC_DOY            Day of year (1-366)                             9    0
> DC_WOY            Week of year (1-52)                             9    0
> DC_WOY_ISO        Week of year (1-53)                             9    0
> DC_QOY            Quarter of year (1-4)                           9    0
> DC_CC             Century (2 digits)                              2    0
> DC_YY             Year (2 digits)                                 2    0
> DC_MM             Month (2 digits)                                2    0
> DC_DD             Day (2 digits)                                  2    0
> DC_CCYYMM         Century, Year, Month CCYYMM (6 digits)          6    0
> DC_DAY_NAM        Day Name (Monday,etc.)                          9
> DC_QTR_NAM        Quarter name (2008Q1)                           6
> DC_WEEKEND        Weekend Flag (Y or N)                           1
> DC_HOLIDAY        Holiday (Y or N)                                1
> DC_BFR_HOL        Day Before Holiday (Y or N)                     1
> DC_AFT_HOL        Day AfterHoliday (Y or N)                       1
> DC_FLL_MN         Full Moon (Y or N)                              1
> DC_SEASON         Season (Spring, Summer, Autumn, Winter)         6
> DC_FIS_YR         Fiscal year (4 digits)                          9    0
> DC_FIS_QTR        Fiscal quarter (1-4)                            9    0
> DC_MO_NM          Month name (January, etc)                       9
> DC_MO_ABV         Month abbreviation (Jan, Feb, etc)              3
> DC_JULIAN         Date in Julian format                           7    0
> DC_CYYMMDD        CYYMMDD packed C = 0 for 1900  & C = 1 for      7    0
> DC_EXCEL          Date in Excel format                            9    0
> DC_WKSTR          Week starting date (the prior Saturday)        10   L
> DC_WKEND          Week ending date (the next Friday)             10   L
> DC_SDLY           Same day last year                             10   L
> DC_CDAY           Current Day (Y/N)                               1
> DC_CWEEK          Current Week (Y/N)                              1
> DC_CMONTH         Current Month (Y/N)                             1
> DC_CQTR           Current Quarter (Y/N)                           1
> DC_CYEAR          Current Year (Y/N)                              1
> DC_CYTD           Current Year to Date (Y/N)                      1
> DC_LDAY           Current Day Last Year (Y/N)                     1
> DC_LWEEK          Current Week Last Year (Y/N)                    1
> DC_LMONTH         Current Month Last Year (Y/N)                   1
> DC_LQTR           Current Quarter Last Year (Y/N)                 1
> DC_LYEAR          Current Year Last Year (Y/N)                    1
> DC_LYTD           Current Year To Date Last Year (Y/N)            1
> DC_PRV_DAY        Previous Day (Y/N)                              1
> DC_PRV_WK         Previous Week (Y/N)                             1
> DC_PRV_MO         Previous Month (Y/N)                            1
> DC_PRV_QTR        Previous Quarter (Y/N)                          1
> DC_PRV_YR         Previous Year (Y/N)                             1
> DC_PRV_FY         Previous fiscal year (Y/N)                      1
> DC_CUR_FY         Current fiscal year (Y/N)                       1
> DC_PRVFYTD        Previous fiscal year to date (Y/N)              1
> DC_CURFYTD        Current fiscal year to date (Y/N)               1
> DC_NTH_DOW        Nth Day of the Week of the month                9    0
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Howie, Bill
> Sent: Wednesday, May 3, 2023 5:00 PM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: [EXTERNAL] Date conversion
>
> Hello all,
>
> I have what is surely a simple question but I can't seem to find an answer to it.  I have a work file that I've created that contains the output of the DSPOBJD command for a particular library.  This file has the field ODUDAT, which is the date last used field.  It's a character field in the MMDDYY format.  I'm using Run SQL Scripts in ACS to then query this file, but I'd like to convert this date to YYYYMMDD format so that I can order the query results by the most recent date.  The only way I can find that comes close to working so far is just to substring the ODUDAT field into the month, day, and year portions and then play with it that way.  I'm sure there's a more elegant method that will just yield a YYYYMMDD field.  Thoughts?
>
> Any ideas on this would be greatly appreciated.  Thanks!
>
> Bill Howie[Related image]<https://www.hc-companies.com/>
> Senior ERP Programmer/Analyst
> Office: 330.486.5818 | Cell: 330.486.5818 | Toll Free: 800.225.7712
> 2450 Edison Blvd., Suite 3, Twinsburg, OH 44087
> hc-companies.com<https://www.hc-companies.com/>
> Leader in Horticultural Containers
> [cid:e0b218dc-8ff8-41fd-81d2-b4ddf650420b]<https://www.linkedin.com/company/the-hc-companies>   [cid:5a6b8cad-48ce-4933-8f87-19d7d96844ba] <https://www.facebook.com/HCCompanies/>    [cid:151d0fd6-bc45-4922-8e5f-cf1f42b0de8f] <https://www.instagram.com/hccompanies/>    [cid:19fc1ef7-7bff-4cae-87af-ec061a1c8770] <https://twitter.com/hc_companies>
>
> Disclaimer
>
> The information contained in this communication from the sender is confidential. It is intended solely for use by the recipient and others authorized to receive it. If you are not the recipient, you are hereby notified that any disclosure, copying, distribution or taking action in relation of the contents of this information is strictly prohibited and may be unlawful.
>
> This email has been scanned for viruses and malware, and may have been automatically archived by Mimecast Ltd, an innovator in Software as a Service (SaaS) for business. Providing a safer and more useful place for your human generated data. Specializing in; Security, archiving and compliance. To find out more visit the Mimecast website.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
>
> [CAUTION! This email originated outside of the organization. Please do not open attachments or click links from an unknown or suspicious origin.]