ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Query

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    When I do DSPFFD on this file then find it is defined as packed with length 7 and also I found that Editing Information as ' Edit word' ' / / '



    Thanks..

    Comment


    • #17
      ...and when I do select * from that file name and then do F4 in front of field names then find it's type as Decimal and length is 7.




      Thanks...

      Comment


      • #18
        Originally posted by John192 View Post
        When I do DSPFFD on this file then find it is defined as packed with length 7 and also I found that Editing Information as ' Edit word' ' / / '



        Thanks..
        it's a physical file and it's DDS is not available.

        Comment


        • #19
          I think MartinTosney is right. Your database table is using EDTWRD or similar to transform the value for display. So if the table holds the number 1191231 then query displays it as 119/12/31.

          But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31.

          So I still think this method will work.
          select * from myfile where field = (int(current date) - 19000000);

          Have you tried it?

          Comment


          • #20
            Originally posted by Vectorspace View Post
            I think MartinTosney is right. Your database table is using EDTWRD or similar to transform the value for display. So if the table holds the number 1191231 then query displays it as 119/12/31.

            But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31.

            So I still think this method will work.
            select * from myfile where field = (int(current date) - 19000000);

            Have you tried it?
            Thanks I tried it but currently i did not get any data for this sql query and output showed like " No Data selected for output"


            When you say "But when comparing the value using SQL, then you need to match the stored value 1191231, not the display transformed value 119/12/31."

            do you mean to say this with respect to program or in which terms i did not understand kindly clarify.


            Thanks ...

            Comment


            • #21
              If the query returns no data then that implies that there are no records where the date column is the current system date.

              Comment


              • #22
                Ok, thanks but what is the reason to reduce 19000000

                from this SQL query:-select * from myfile where field = (int(current date) - 19000000);


                ?


                Thanks...

                Comment


                • #23
                  Because int(current date):returns the current date as a number in YYYYMMDD format. Your date column is a number in CYYMMDD format. Subtracting 19000000 is a quick and easy way to convert from YYYYMMDD numeric to CYYMMDD numeric.


                  "current date" is a SQL keyword that returns the current system date in the date data type

                  int(x) converts x to an integer numeric. Where x is a date, int(x) would convert the date to a number, in format YYYYMMDD
                  E.g. if the current date is 31st December 2021, int(current date) would return the number 20211231

                  CYYMMDD date format uses a single digit to represent the century, where 20th century (1900-1999) = 0 and 21st century (2000-2099) = 1.
                  E.g. if the current date is 31st December 2021, in numeric CYYMMDD it would be 1211231

                  20211231 minus 1211231 = 19000000. This is true for any date - when the dates are both stored as numbers, the difference between the YYYYMMDD and CYYMMDD is always 19000000.

                  So converting the current date to a number in YYYYMMDD format and then subtracting 19000000 is a quick and easy way to convert to numeric CYYMMDD

                  Comment


                  • #24
                    no it's just defined as numeric field with 7 length and zero decimal position and if the same date is in C,YYM,MDD format for example 1,211,217 then how we will fetch for current date's records from same file?


                    thanks...


                    Comment


                    • #25
                      Vectorspace already gave you the query for this in post #19, and you even quote it in post #22. He's also kindly explained the logic in each part of the conversion, from the current date to a 7-digit numeric representation.

                      I'm not sure what else you are expecting here!

                      Comment


                      • #26
                        Or add 19000000 to your CYYMMDD field in your code to match the YYYYMMDD in current_date. Then it might be easier if you need to calculate # of days between dates.

                        Comment


                        • #27
                          Originally posted by John192 View Post
                          no it's just defined as numeric field with 7 length and zero decimal position and if the same date is in C,YYM,MDD format for example 1,211,217 then how we will fetch for current date's records from same file?


                          thanks...

                          I think we gave you already almost all alternatives for converting a 7 digit numeric date into a real date for to compare it with the current date.
                          Please read the answers you got before instead of posting the same question over and over again.

                          Comment

                          Working...
                          X