Using SQL to compare yyyyddd to mmddyyyy

[JustinWithoutAnE]

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.

[paolinosal]

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
;

Bye

[wegrace]

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                                                                                                                                                                                                                        
deterministic                                                                                                                                                                                                                      
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                                                                                                                                                      

begin                                                                                                                                                                                                                              

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'));                                                                                                                                                              

else                                                                                                                                                                                                                                

  return null;                                                                                                                                                                                                                      
end case;                                                                                                                                                                                                                          

end

Walt

[Vectorspace]

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

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_timestampformat.html

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

[wegrace]

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.

Walt

[JustinWithoutAnE]

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?

[B.Hauser]

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,
       x.*
   from x;

Birgitta

[Vectorspace]

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]

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.

Walt

[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?

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;

[JustinWithoutAnE]

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.

[Vectorspace]

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

[Vectorspace]

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.

[wegrace]

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)

[Vectorspace]

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.

[wegrace]

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.

Walt