SQL - subtracting text/dates to calculate number of Days

[Fredek]

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?

 

[Leandroarb]

You can find out how many days there are between the two dates with the function

DateDiff ( interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

For transform the format, use the text functions.

Ex:

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

Cheers

 

[Fredek]

Hi Leo,

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

 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;

 

[Leandroarb]

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

 

[Fredek]

Gave it ago, but still the same DateDiff error! Am I missing something?

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;

 

[Fredek]

I think it may be because it is Oracle ODBC database?

sum(VALUE_BAL_USEQ)/   (to_char(to_date('20180714', 'yyyymmdd'), 'ddmmyyyy') - to_char(to_date('20180712', 'yyyymmdd'), 'ddmmyyyy')) As AVG_USD_BALANCE

 

[xenou]

Yes, if you are using Oracle then there would be different functions for dates.

 

[Joe4]

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.

 

[Fredek]

Apologies guys, I am very new to this.