I was asked how SQL could compute the number of days difference between two dates columns, where one column might contain NULL. The timestamp_format and timestampdiff built-in functions get the job done, with a little help from coalesce.

Employment Days.sql

create table toystore.employee_deetz (
      Company_Id bigint, 
      Employee_Id bigint, 
      FirstName varchar(100) for sbcs data,
      SurName varchar(100) for sbcs data, 
      Employed_Start_Date date, 
      Employed_End_Date date);
      
insert into toystore.employee_deetz values
  (101, 10001, 'Scott', 'Forstie', '07/11/1989', NULL);
      
insert into toystore.employee_deetz values
  (101, 10002, 'Tim', 'Rowe', '08/22/1997', '07/04/2012');
      
insert into toystore.employee_deetz values
  (101, 10003, 'Sue', 'Romano', '04/14/2021', NULL);

-- Overcome that the end date of employment may not exist
select coalesce(Employed_End_Date, current date) as overcome_null, z.*
  from toystore.employee_deetz z;

-- Transform the dates into timestamps
select 
  timestamp_format(varchar(coalesce(Employed_End_Date, current date)), 'MM/DD/YYYY') as end_timestamp, 
  timestamp_format(varchar(Employed_Start_Date), 'MM/DD/YYYY') as start_timestamp, 
  z.*
  from toystore.employee_deetz z;

-- Use timestampdiff to compute the number of days of employment  
select timestampdiff(16, 
         cast(timestamp_format(varchar(coalesce(Employed_End_Date, current date)), 'MM/DD/YYYY') - 
         timestamp_format(varchar(Employed_Start_Date), 'MM/DD/YYYY') as char(22))) 
           as employment_days, 
         z.* 
  from toystore.employee_deetz z ; 
          
-- Find employees that have worked > 100 days
select timestampdiff(16, 
         cast(timestamp_format(varchar(coalesce(Employed_End_Date, current date)), 'MM/DD/YYYY') - 
         timestamp_format(varchar(Employed_Start_Date), 'MM/DD/YYYY') as char(22))) 
           as employment_days, 
         z.* 
  from toystore.employee_deetz z 
where 
  timestampdiff(16, 
         cast(timestamp_format(varchar(coalesce(Employed_End_Date, current date)), 'MM/DD/YYYY') - 
         timestamp_format(varchar(Employed_Start_Date), 'MM/DD/YYYY') as char(22))) 
         > 100
  order by 1 desc; 

  

line 20 has an inadvertent space in it
from toystore.e mployee_deetz z;
should be
from toystore.employee_deetz z;

My date data type returns with the format of 'YYYY-MM-DD' and therefore running this script as is errors.

Observe the format returned when running the query on lines 19 and 20. If a date format other than 'MM/DD/YYYY' is displayed, then use find-n-replace to update all occurrences of the format-string parameter in this script.

Thank you!!!Sent from my iPhoneOn Nov 17, 2022, at 9:28 AM, stevee-bee ***@***.***> wrote:Re: forstie/Employment ***@***.*** commented on this gist.My date data type returns with the format of 'YYYY-MM-DD'. Observe the format returned when running the query on lines 19 and 20. If a date format other than 'MM/DD/YYYY' is displayed, then use find-n-replace to update all occurrences of the format-string in this script.—Reply to this email directly, view it on GitHub or unsubscribe.You are receiving this email because you subscribed to a thread.Triage notifications on the go with GitHub Mobile for iOS or Android.                                                           

line 20 has an inadvertent space in it from toystore.e mployee_deetz z; should be from toystore.employee_deetz z;

Fixed... thanks and not sure how that got in there!

Yes, the example is specific to MM/DD/YYYY. (*USA)
Did you update the script to match your preferred data format? (YYYY-MM-DD) (*ISO)

Did you update the script to match your preferred data format? (YYYY-MM-DD) (*ISO)

Yes, I did, thanks.

Did you update the script to match your preferred data format? (YYYY-MM-DD) (*ISO)

Yes, I did, thanks.

Ok, cool. Thanks for pointing out the typo.