Sponsored Links
Sponsored Link

sponsored links



No announcement yet.

Using SQL to compare yyyyddd to mmddyyyy

  • Filter
  • Time
  • Show
Clear All
new posts

  • Using SQL to compare yyyyddd to mmddyyyy

    Hi all, I am trying to compare the date field of two files: one uses the day of the year format yyyyddd and the other uses mmddyyyy. Both fields are defined as decimals. I can convert mmddyyyy to yyyymmdd but I don't know how to convert it to day of the year or vice versa using SQL.

    I've done some searching and found some old posts where they have a somewhat complicated math to convert the seven-digit Julian date but I was hoping that something better has become available since those posts were made.

    Some background:
    File A uses a seven digit decimal field as a dare field with the format yyyyddd
    File B uses a nine digit decimal field as a date field with format mmddyyyy

    I need to extract all records in File A where the date is equal to the date on File B.

  • #2
    If I have understand your problem try this:

    Declare global temporary table Datedays  (
    datadays numeric(7, 0) not null with default 0,
    textdays varchar(10) );    
    Declare global temporary table Dateusa  (
    datausa numeric(8, 0) not null with default 0,
    textusa varchar(10));   
    Insert into datedays 
    values(2018073, 'Oggi'),
    (2018072, 'Ieri'),
    (2017072, 'Lastyear')
    Insert into dateusa 
    values(03142018, 'Oggi'),
    (03132018, 'Ieri'),
    (03182018, 'future'); 
    Select datedays.* , dateusa.*,  varchar_format(char(datadays), 'MMDDYYYY') 
    From datedays, dateusa
    where varchar_format(char(datadays), 'MMDDYYYY') = datausa


    • #3
      You could try using the function I attached below to convert both numeric dates to real dates, and then compare them.

      create or replace function cdate                                                                                                                                                                                                    
        theDate numeric(8,0)                                                                                                                                                                                                              
       ,formatCode int                                                                                                                                                                                                                    
      returns date                                                                                                                                                                                                                        
      specific SYDATE001                                                                                                                                                                                                                  
      language sql                                                                                                                                                                                                                        
      contains sql                                                                                                                                                                                                                        
      not fenced                                                                                                                                                                                                                          
      no external action                                                                                                                                                                                                                  
      allow parallel                                                                                                                                                                                                                      
      set option Commit=*NONE      ,DatFmt=*ISO   ,DynUsrPrf=*OWNER ,UsrPrf=*OWNER   ,DlyPrp=*YES                                                                                                                                        
                ,CloSqlCsr=*ENDMOD ,SQLpath=*LIBL ,DftRdbCol=*NONE  ,DbgView=*SOURCE                                                                                                                                                      
      declare exit handler for sqlexception                                                                                                                                                                                              
        return null;                                                                                                                                                                                                                      
      -- Numeric *LOVAL received                                                                                                                                                                                                          
      if theDate = 0 then                                                                                                                                                                                                                
        return date('0001-01-01');                                                                                                                                                                                                        
      end if;                                                                                                                                                                                                                            
      -- Numeric *HIVAL received                                                                                                                                                                                                          
      if (theDate = 99999999) or (theDate = 999999) then                                                                                                                                                                                  
        return date('9999-12-31');                                                                                                                                                                                                        
      end if;                                                                                                                                                                                                                            
      case formatCode                                                                                                                                                                                                                    
      when 1 then -- YYYY/MM/DD                                                                                                                                                                                                          
        return date(timestamp_format(digits(theDate), 'YYYYMMDD'));                                                                                                                                                                      
      when 2 then -- MM/DD/YYYY                                                                                                                                                                                                          
        return date(timestamp_format(digits(theDate), 'MMDDYYYY'));                                                                                                                                                                      
      when 3 then -- MM/DD/YY                                                                                                                                                                                                            
        return date(timestamp_format(right(digits(theDate), 6), 'MMDDYY'));                                                                                                                                                              
        return null;                                                                                                                                                                                                                      
      end case;                                                                                                                                                                                                                          
      Last edited by wegrace; March 14th, 2018, 09:31 AM.


      • #4
        To add some context to the above answers, built-in SQL function timestamp_format can be used to convert a string representation of a date or timestamp (in any number of formats) to the timestamp format

        E.g., if fileA has column julDate which is a 7p0 julian date yyyyddd with value 2018100 then this will convert it to a timestamp
        select timestamp_format(digits(julDate),'YYYYDDD')
        from fileA;
        Result is a timestamp with value '2018-04-10 00:00:00.000000'
        Digits is used to convert the number 2018100 to string '2018100'

        You can do something similar with fileB's USA format date. But because you say the column is 9 digits numeric rather than 8, digits() for date 04102018 would produce string '004102018' which is invalid.
        So first we would have to cast it to an 8 digit decimal
        E.g. if fileB has column usaDate 9p0 USA format date with value 04102018, then this will convert it to a timestamp:
        select timestamp_format(digits(decimal(usaDate,8,0))),'MMDDYYYY')
        from fileB;
        Result is a timestamp with value '2018-04-10 00:00:00.000000'

        But if you were to join the files using this method, it would be slow because no keys can be used. So maybe you want to convert one date entirely to the format of the other.
        I'm not sure how to convert to julian date, so I will show you how to convert to numeric USA date:

        You can convert the date of a timestamp to numeric USA format (MMDDYYYY as a numeric) using a few more functions:
        * date to extract the date portion of the timestamp
        * char with format 'usa' to convert it to a string in 'mm/dd/yyyy' format (RPG has date format usa0 to get mmddyyyy, unfortunately SQL does not)
        * replace to remove the / separators to get string 'mmddyyyy'
        * decimal to convert it to a number
        So to get the julian date all the way to numeric mmddyyyy in 8p0:

        select decimal(replace(char(date(timestamp_format(digits(julDate),'YYYYDDD')),usa),'/',''),8,0)
        from fileA;
        You can directly compare this with the usaDate column in fileB, so it will use any keys on that column for faster lookup


        • #5
          Thank you for adding the clarification, VectorSpace.

          I completely spaced on the Julian Date; I was seeing it as YYYYMMDD.

          You are correct that this is not the fastest method, but imo the easiest to implement (adding the Julian conversion to the function of course).

          I believe though that your last statement regarding "faster lookup" is not entirely accurate. Both date conversions would still need to be made before a comparison can be made since *USA date formats cannot be compared with each other without some type of conversion where the Century and Year are the most significant digits.

          For this scenario, it seems that it would be quicker to convert the *USA date to Julian, and then compare.



          • #6
            Just tried out Vectorspace's suggestion and it was just what I was looking for. Thank Vectorspace!

            If I use an alias to name the result of the conversion is there a way to use the alias in the Where clause?
            Last edited by JustinWithoutAnE; March 15th, 2018, 01:15 AM.


            • #7
              Just a comment! For Converting a character Julian Date into a real data, you do not need anything else than DATE.
              Here an example with multiple ways converting numeric dates:
              With x as (Select Cast(ISONum  as Dec(8, 0)) ISONum,
                                Cast(USANum  as Dec(8, 0)) USANum,
                                Cast(ISOChar as Char(8))   ISOChar,
                                Cast(USAChar as Char(8))   USAChar,
                                Cast(JulNum  as Dec(7, 0)) JulNum,
                                Cast(JulChar as Char(7))   JulChar
                           from (Values(20180301, 03012018, '20180301', '03012018', 2018060, '2018060'),
                                       (20171231, 12312017, '20171231', '12312017', 2017365, '2017365'),
                                       (20171030, 10302017, '20171030', '10302017', 2018032, '2018032')) x  
                                       (ISONum, USANum, ISOChar, USAChar, JulNum, JulChar))
              Select Date(Digits(ISONum) concat '000000')                                          ISODate,
                     Date(Right(Digits(USANum), 4) concat Left(Digits(USANum), 4) concat '000000') USADate,
                     Date(ISOChar concat '000000')                                                 ISODate2,
                     Date(Right(USAChar, 4) concat Left(USAChar, 4) concat '000000')               USADate2,
                     Date(Digits(JulNum))                                                          JulDate1,
                     Date(JulChar)                                                                 JulDate2,
                     Date(Timestamp_Format(Digits(ISONum), 'YYYYMMDD'))                            ISODate3,
                     Date(Timestamp_Format(Digits(USANum), 'MMDDYYYY'))                            USADate3,
                     Date(Timestamp_Format(ISOChar, 'YYYYMMDD'))                                   ISODate4,
                     Date(Timestamp_Format(USAChar, 'MMDDYYYY'))                                   USADate4,
                 from x;


              • #8
                Originally posted by wegrace View Post
                I believe though that your last statement regarding "faster lookup" is not entirely accurate. Both date conversions would still need to be made before a comparison can be made since *USA date formats cannot be compared with each other without some type of conversion where the Century and Year are the most significant digits.
                It depends on what kind of comparison you are looking at.

                If you want to order them or do greater than/less than, then you are right as USA dates alphabetic/numeric sort order does not match date sort order (that is why I would always recommend storing dates in ISO format, yyyymmdd)

                But if you just want to join fileA to fileB records with equal dates, then you can just convert one of the dates to the other, e.g.:
                select * from fileA f1
                inner join fileB f2 on convert-jul-to-usa(f1.julDate) = f2.usaDate
                And if there is a key/access path on fileB's usaDate column, it will be used


                • wegrace
                  wegrace commented
                  Editing a comment
                  I gotta stop trying to help with such a lack of sleep. 0-(

                  I miss too many details, such as 'equal dates'.

                  Thanks for setting me straight.


              • #9
                Originally posted by JustinWithoutAnE View Post
                If I use an alias to name the result of the conversion is there a way to use the alias in the Where clause?
                Not directly no. The where clause can only reference column names. So for a simple statement you would have to do this:
                select date(julDate) from filea
                where date(julDate) = somevalue
                Or you could use a subselect or a "with as", but I don't know if that has efficiency implications:
                // subselect:
                select convDate from (
                  select date(julDate) as convDate from filea) as fa
                where convDate = somevalue;
                // with as (pseudo-table)
                with fa as (
                  select date(julDate) as convDate from filea)
                select convDate from fa
                where convDate = somevalue;


                • #10
                  So here's what I ended up doing:
                  Select a.ItemNum
                  From FileA a, FileB b
                  where a.product in (33,34)
                  and a.branch = b.branch
                  and replace(char(date(timestamp_format(digits(PostingDate),'YYYYDDD')),usa),'/','')
                  = char(CurrentDate)
                  It is slow and not very efficient though. Any thoughts on how I can make execution faster? I am planning to embed this code in an RPG program.
                  The aim is to get a list of Items that were posted today for a specific product.


                  • #11
                    It's slow because it's doing a scalar conversion on both dates, which means it cannot use either's access path (I am assuming the dates are among the key fields)

                    You need to limit the conversion to one only.
                    CurrentDate is a decimal numeric, right? Try this:

                    Select a.ItemNum
                    From FileA a, FileB b
                    where a.product in (33,34)
                    and a.branch = b.branch
                    and decimal(replace(char(date(timestamp_format(digits(PostingDate),'YYYYDDD')),usa),'/',''),8,0)
                    = CurrentDate
                    If fileB has an index on branch and currentdate, it should be faster this way


                    • #12
                      Another option is something called a Date Dimension Table, which is a table where each row has the same date in a variety of formats. Table DATE_CONV in this thread is an example: http://www.code400.com/forum/forum/i...days-to-a-date

                      This would be a new table that has a column for numeric usa date and a column for numeric julian date (and possibly columns for other date types too), and an index on each

                      You can then join FileA.PosingDate -> DateDim.JulianDate, and DateDim.USADate -> FileB.CurrentDate

                      Then as long as you create the appropriate indexes, it's all fast.


                      • #13
                        Since we only want records equal to the current date, it seems that the execution would be faster if we just convert the current date to the formats we need, and then compare the table dates to the converted current date instead of trying to convert every row date in the two tables.

                        Try this (replacing a.date and b.date with your respective column names of course)

                        select a.itemNum
                        from fileA a
                          join fileB b on b.branch = a.branch
                        where a.product in (33,34)
                          and a.date = dec(year(current_date) || digits(dec(days(current_date) - days(date(year(current_date) || '-01-01')),3)),7)
                          and b.date = dec(replace(char(current_date, USA), '/', ''), 8, 0)
                        Last edited by wegrace; March 15th, 2018, 08:37 AM.


                        • #14
                          I could be wrong, but I thought currentDate in JustinWithoutAnE's SQL was supposed to be a column in fileA, not the literal current system date.


                          • #15
                            VectorSpace, you are correct.

                            Apparently my abysmal reading comprehension is not getting any better this week.

                            So, I will just exit stage left.

                            Good Luck.



                            sponsored links