SQL - subtracting text/dates to calculate number of Days

Fredek

Board Regular
Joined
Mar 8, 2011
Messages
65
Hi

If I have two dates in text format (20180329 and 20180424) how can I work out the number of days between them?

I need this to be in SQL as the two dates are coming from Access forms. Subtracting only works for dates in the same month. Can I convert them into dates in SQL and then use a DATEIFF?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You can find out how many days there are between the two dates with the function
Code:
DateDiff ( interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )
For transform the format, use the text functions.

Ex:
Code:
DataDiff('d',DateSerial(Left(StringDate1),Mid(StringDate1,5,2),Rigth(StringDate1,2)), DateSerial(Left(StringDate2),Mid(StringDate2,5,2),Rigth(StringDate2,2)))

Cheers
 
Upvote 0
Hi Leo,

Thank you for your answer, I used your code, however I am getting a DateDiff Invalid Identifier Error. Any idea why?

Code:
 Select 
EQ_branch_Name, 
Acct_Grp_Desc, 
Acct_Num, 
Acct_Name, 
Acct_Curr_Cde, 
(Case when Grp_Cde is Null then substr(acct_num,5,6) 
else Grp_Cde End) as Grp_Cde, 
(Case when Grp_Desc is Null then substr(acct_num,5,6) 
else Grp_Desc End) as Grp_Desc, 
sum(VALUE_BAL_USEQ)/ DateDiff('d',DateSerial(Left(20180402,4),Mid(20180402,5,2),Right(20180402,2)), DateSerial(Left(20180329,4),Mid(20180329,5,2),Right(20180329,2))) As AVG_USD_BALANCE
FROM 
dwrpt.t_risk_merge where      
bal_dte_id>='20180329' and bal_dte_id<='20180402' 
And (GRP_CDE IS NULL OR GRP_CDE NOT IN ( 'CBN-PA','RMF-NS','RMF-PA')) 
GROUP BY eq_branch_name, acct_grp_desc, acct_num, acct_name, acct_curr_cde,GRP_CDE, GRP_DESC    
HAVING AVG(VALUE_Bal_USEQ)>0  
ORDER BY eq_branch_name, acct_grp_desc, acct_num, acct_name, acct_curr_cde;
 
Last edited:
Upvote 0
Hi Fredek! Try this:
DateDiff('d',DateSerial(Left('20180402',4),Mid('20180402',5,2),Right("20180402",2)), DateSerial(Left(20180329,4),Mid('20180329',5,2),Right('20180329',2)))
 
Upvote 0
Gave it ago, but still the same DateDiff error! Am I missing something?


Code:
Select 
EQ_branch_Name, 
Acct_Grp_Desc, 
Acct_Num, 
Acct_Name, 
Acct_Curr_Cde, 
(Case when Grp_Cde is Null then substr(acct_num,5,6) 
else Grp_Cde End) as Grp_Cde, 
(Case when Grp_Desc is Null then substr(acct_num,5,6) 
else Grp_Desc End) as Grp_Desc, 
sum(VALUE_BAL_USEQ)/ DateDiff('d',DateSerial(Left(20180302,4),Mid(20180302,5,2),Right(20180302,2)), DateSerial(Left(20180329,4),Mid(20180329,5,2),Right(20180329,2))) As AVG_USD_BALANCE
FROM 
dwrpt.t_risk_merge where      
bal_dte_id>='20180329' and bal_dte_id<='20180402' 
And (GRP_CDE IS NULL OR GRP_CDE NOT IN ( 'CBN-PA','RMF-NS','RMF-PA')) 
GROUP BY eq_branch_name, acct_grp_desc, acct_num, acct_name, acct_curr_cde,GRP_CDE, GRP_DESC    
HAVING AVG(VALUE_Bal_USEQ)>0  
ORDER BY eq_branch_name, acct_grp_desc, acct_num, acct_name, acct_curr_cde;
 
Upvote 0
I think it may be because it is Oracle ODBC database?


Code:
sum(VALUE_BAL_USEQ)/   (to_char(to_date('20180714', 'yyyymmdd'), 'ddmmyyyy') - to_char(to_date('20180712', 'yyyymmdd'), 'ddmmyyyy')) As AVG_USD_BALANCE
 
Upvote 0
Yes, if you are using Oracle then there would be different functions for dates.
 
Upvote 0
I think it may be because it is Oracle ODBC database?
For future reference, that is a critical detail that you will want to be sure to mention in your original post (especially since this is not an Oracle forum).
That way, people won't waste time recommending answers that won't work for you.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top