dates and time - IBM AS400

This is a discussion on dates and time - IBM AS400 ; Hauser@sss-software.de wrote: > BTW DD/MM/YYYY is not a valid format for a date! I seriously hope the British and commonwealth nations, most of Europe, the Middle East, the Subcontinent, much of SE Asia, Africa and others don't find out that ...

+ Reply to Thread
Results 1 to 13 of 13

Thread: dates and time

  1. Re: dates and time

    Hauser@sss-software.de wrote:
    > BTW DD/MM/YYYY is not a valid format for a date!


    I seriously hope the British and commonwealth nations, most of Europe,
    the Middle East, the Subcontinent, much of SE Asia, Africa and others
    don't find out that they are using an invalid date format.


    Rj.

  2. dates and time

    Can anyone point me to some good information on date handling?
    I am importing some records from a PC file that are formatted as
    MM/DD/CCYY and time as HH:MM:SS. By that I mean, the "/" and the ":"
    are part of the date.

    When I bring these fields to the as/400, if I intend to date and time
    processing with them, do I want to leave them in this format or change
    them. I haven't worked with date/time functions that much. Do I want
    to remove the "/" and the ":" from the fields??

    I know I am going to want to know what Day of the week the date is,
    possibly the interval between two dates, and probably the difference
    between two times on the same day.

    As I said, I haven't dealt with dates that much, but since I am
    creating a new file for this project, I want to do it right. I can
    load the data from the PC into PF fields in whatever format I want; I
    just want to do it the best way to simplify date and time operations
    later.

    Anyone have any suggestions of a web-site or an IBM manual that would
    give me more information/suggestions on this?

    Thanks
    ga

    ga
    nospam@nospam.fmctc.com

  3. Re: dates and time

    Hi,

    on System i or better in the database a date is always stored in a
    4Byte Integer Value representing the number of days since x (Scaliger
    Number). A date format is only used to make this binary value
    readeable.

    A time is always stored in a 3 byte integer value, the first byte
    represents the hours, the second the minutes and the last byte the
    seconds.

    If you suceed inserting your date and time fields correctly into a
    date or time field in a database table, the date and time format does
    not matter.

    SQL provides a couple of scalar functions among them:
    DAYOFWEEK: Returns the numeric day of week for a date or timestamp or
    a valid character representation of a date or timestamp. Sunday is 1
    and Saturday is 7.
    DAYOFWEEK_ISO: is the same as DAYOFWEEK except Monday is 1 and
    Saturday is 7.

    Birgitta

  4. Re: dates and time

    Character representations of a date in the format of MM/DD/YYYY and
    time HH:MM:SS can be converted directly to a valid date.

    Birgitta

  5. Re: dates and time

    More questions...I'm no.t much of an RPG whiz, as you will be able to
    tell

    Okay...let's say I decide to leave the date in a character format of
    MM/DD/YYYY (i.e. 05/04/2008) in RPG so it's a character field

    If I wanted to populate a 14 element array with the prior 14 dates and
    a second array with what day of week (numerical representation), can I
    do something like this using subdur? Or is it better to have my date
    as 05042008 or 20080504 or what...

    For instance, can I use 05/04/2008 for "datein"
    and do something like:

    z-add 15 x
    x doueq 1
    x sub 1 x
    y add 1 y
    datein sub x dat,y

    call a subroutine todetermine day of week here???
    move dow dowa,y

    enddo

    Will my dat,x be in the same format MM/DD/CCYY

    When I'm done I want my arrays to look something like this:
    I really don't care when I'm done if date array is 04/21/2008 or
    04212008 format, and I really don't care if the day of week for Sunday
    starts with "0" or "1".

    Dat array dowa

    04/21/2008 0
    04/22/2008 1
    04/23/2008 2
    04/24/2008 3
    04/24/2008 4
    04/25/2008 5
    04/26/2008 6
    04/27/2008 0
    04/28/2008 1
    04/29/2008 2
    04/30/2008 3
    05/01/200 4
    05/02/2008 5
    05/03/2008 6
    05/04/2008 0

    Am I on the right track or off the beaten path?


    "Hauser@sss-software.de" wrote:

    >Character representations of a date in the format of MM/DD/YYYY and
    >time HH:MM:SS can be converted directly to a valid date.
    >
    >Birgitta



    ga
    nospam@nospam.fmctc.com

  6. Re: dates and time

    Hauser@sss-software.de wrote:
    > Character representations of a date in the format of MM/DD/YYYY and
    > time HH:MM:SS can be converted directly to a valid date.
    >
    > Birgitta


    Except if the Day is less than 13 and you live outside the US where
    DD/MM/YYYY (or variants) are the main format.

    Use the ISO formats (YYYY-MM-DD) where possible with SQL and there will
    never be any misunderstanding when your product/services go international.

    Rj.

  7. Re: dates and time

    If we talk about SQL:
    SQL can recognize a character representation of a date with a 4 digit
    year and predefined date separators in one of the following formats:
    YYYY-MM-DD, DD.MM.YYYY, MM/DD/YYYY

    Just try:
    CREATE TABLE MyTable (MyDate Date Not Null)

    INSERT INTO MyFile Values('2008-04-30')
    INSERT INTO MyFile Values('30.04.2008')
    INSERT INTO MyFile Values('04/30/2008')
    ....
    after try
    INSERT INTO MyFile Values('30/04/2008')

    BTW DD/MM/YYYY is not a valid format for a date!
    (Direct Conversion will only be possible with release V6R1 and the
    scalar function TIMESTAMP_FORMAT)

    A conversion into a real date (with the scalar function DATE) is only
    necessary, if date calculations (adding/substracting dates/times) must
    be done.

    In RPG the date format of the "Non-"Date must be specified in either
    the MOVE-Command or built-in-function.
    For DateFormats with a for digit year, the following formats are
    allowed:
    *ISO / *JIS = 'YYYY-MM-DD'
    *USA = 'DD.MM.YYYY'
    *EUR = 'MM/DD/YYYY'

    Also 'DD/MM/YYYY' is not a valid date format!

    MyDate = %Date(MyCharDate: *USA);

    @ga
    Use RPGIV instead of RPGIII.
    With RPGIV you have a bunch of built-in-functions for date
    calculation.
    If it is not possible to use RPGIV use embedded SQL, which delivers
    all functions you need and can be embedded into RPGIII (the member
    attribut must be changed to SQLRPG!):

    C/EXEC SQL
    C+ SET AYW = DAYOFWEEK_ISO(ATEA)
    C/EXEC SQL

    Birgitta

  8. Re: dates and time

    > *USA = 'DD.MM.YYYY'
    > *EUR = 'MM/DD/YYYY'


    Typo here. Actually *USA = mm/dd/yyyy
    and *EUR = dd.mm.yyyy


    > BTW DD/MM/YYYY is not a valid format for a date!
    >


    Actually it is. Its the *DMY format with default seperator


    > I seriously hope the British and commonwealth nations, most of Europe,

    the Middle East, the Subcontinent, much of SE Asia, Africa and others
    don't find out that they are using an invalid date format.

    We won't tell them if you won't. Seriously, I have seen dd/mm/yyyy
    from some of these locations, but usually I see dd.mm.yyyy





  9. Re: dates and time

    Graybeard wrote:
    >> *USA = 'DD.MM.YYYY'
    >> *EUR = 'MM/DD/YYYY'

    >
    > Typo here. Actually *USA = mm/dd/yyyy
    > and *EUR = dd.mm.yyyy
    >
    >
    >> BTW DD/MM/YYYY is not a valid format for a date!
    >>

    >
    > Actually it is. Its the *DMY format with default seperator
    >
    >
    >> I seriously hope the British and commonwealth nations, most of
    >> Europe,

    > the Middle East, the Subcontinent, much of SE Asia, Africa and others
    > don't find out that they are using an invalid date format.
    >
    > We won't tell them if you won't. Seriously, I have seen dd/mm/yyyy
    > from some of these locations, but usually I see dd.mm.yyyy


    In the Netherlands we do dd-mm-yyyy LOL



  10. Re: dates and time

    > > Actually it is. *Its the *DMY *format with default seperator

    Wrong, just have a look into the System i manuals,
    for example into the RPG Reference and search for date formats.
    There are only 3 date formats with a 4 digits year, i.e. *EUR
    (DD.MM.YYYY), *USA (MM/DD/YYYY) and *ISO (YYYY-MM-DD) which is equal
    to *JIS format.

    For dates with a 2 digit year, several separators (. , ; - and blank)
    can be choosen.

    An other example is: Just try to change the date format with STRSQL,
    F13, Option 1. Change the date format to (EUR, ISO, USA) and try to
    sepecify a date separator.

    >
    > >> I seriously hope the British and commonwealth nations, most of
    > >> Europe,

    > > the Middle East, the Subcontinent, much of SE Asia, Africa and others
    > > don't find out that they are using an invalid date format.

    >

    Sorry I didn't mean DD-MM-YYYY or DD/MM/YYYY is invalid at all, but
    it is not valid according the System i reference manuals.

    Birgitta

  11. Re: dates and time

    ga wrote:
    > More questions...I'm no.t much of an RPG whiz, as you will be able to
    > tell
    >
    > Okay...let's say I decide to leave the date in a character format of
    > MM/DD/YYYY (i.e. 05/04/2008) in RPG so it's a character field
    >
    > If I wanted to populate a 14 element array with the prior 14 dates and
    > a second array with what day of week (numerical representation), can I
    > do something like this using subdur? Or is it better to have my date
    > as 05042008 or 20080504 or what...
    >
    > For instance, can I use 05/04/2008 for "datein"
    > and do something like:
    > ...


    The rest of the responses so far have been about SQL, but it sounds like
    you are not using SQL ...

    If datein is a character field, in the form MM/DD/YYYY, you will need to
    convert it to a "real" date to use it for any date arithmetic.

    Here is an RPG IV program that does something like what you want. I
    hope it will show you in general how you can do date arithmetic.

    H dftactgrp(*no)
    D datein s 10a inz('05/04/2008')
    D dat s 10a dim(15)
    D dow s 5p 0 dim(15)
    D tempdate s d
    D getDayOfWeek pr 5p 0
    D date d const
    D y s 5p 0

    * Get 15 days before datein, in a temp ("real" date field)
    C eval tempdate = %date(datein : *USA/)
    C - %days(15)
    C for y = 1 to 15
    * Add 1 to the temp date, so the first date will be 14 days
    * before, the next will be 13 days before etc
    C eval tempdate = tempdate + %days(1)
    C eval dat(y) = %char(tempdate : *usa/)
    C eval dow(y) = getDayOfWeek(tempdate)
    C endfor
    C return

    * Procedure getDayOfWeek: Returns 0 for Sunday, 1 for Monday etc

    P getDayOfWeek b
    D getDayOfWeek pi 5p 0
    D date d const
    D oneSunday c d'2008-05-04'
    D days s 5p 0
    D dayOfWeek s 5p 0
    * - get the number of days between the input date and a known Sunday
    * - get the remainder of that number and 7
    C date subdur oneSunday days:*days
    C eval dayOfWeek = %rem(days:7)
    C if dayOfWeek < 0
    C eval dayOfWeek = dayOfWeek + 7
    C endif
    C return dayOfWeek
    P getDayOfWeek e

  12. Re: dates and time

    Barbara,

    You are too kind!! This is exactly what I was looking for.

    In fairness to myself, I actually wrote it and got it to work before I
    got your reply, but your program is a lot more of a "toolkit" program
    so I am hanging onto it for a very long time, and know it will prove
    to be a very useful resource in the future.

    Sometimes I drive myself crazy because I work on programs in RPG II,
    RPG III, RPG ILE...keeping straight what you can do where is a
    challenge.

    I have gotten so much great help from this group, on programming
    techniques, SQL, etc. Just a wonderful place because everyone is so
    generous in sharing their talents and knowledge.

    Thank you again, Barbara,
    ga


    ga
    nospam@nospam.fmctc.com

  13. Re: dates and time

    il 04/05/2008 14.06, Scrive DBDriver 40467520:
    > Hauser@sss-software.de wrote:
    >> BTW DD/MM/YYYY is not a valid format for a date!

    >
    > I seriously hope the British and commonwealth nations, most of Europe,
    > the Middle East, the Subcontinent, much of SE Asia, Africa and others
    > don't find out that they are using an invalid date format.

    Maybe, but I'sure ISO do.

    --
    Dr.Ugo Gagliardelli,Modena,ItalyCertifiedUindoscrasherAñe joAlcoolInside
    Spaccamaroni andate a cagare/Spammers not welcome/Spammers vão à merda
    Spamers iros a la mierda/Spamers allez vous faire foutre/Spammers loop
    schijten/Spammers macht Euch vom Acker/Spamerzy wypierdalac'

+ Reply to Thread