Last active
November 18, 2022 17:40
-
-
Save forstie/44dd2407f16b6861fe2ddf2a263873a0 to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
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 befrom 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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.