Chaya Copy and paste the Create statement I sent (actually below. Then run the file. If you want you can paste it at the R:> but you will have to remove the + plus signs.
Buddy From: [email protected] [mailto:[email protected]] On Behalf Of Chaya Goldberg Sent: Friday, June 9, 2017 2:25 PM To: [email protected] Subject: RE: [RBASE-L] - difference between two dates I tried using dextract, but still having trouble. Maybe the issue is that I’m not defining the table correctly? I’m creating a temporary table on the SQL server, then using SATTACH and PROJECT to create temporary RBASE tables. In all cases, getting error messages that the new tables are undefined. Version 1. SSQL SELECT + ((dextract(A.startdate))-(dextract(C.eireferraldate))) AS initialcontactlag + INTO #tinitialcontact + WHERE … SATTACH TEMP #tinitialcontact AS tservcontact PROJECT trbcontact FROM tservcontact USING * This worked without the dextract, so not sure what’s causing the issue. Version 2. SSQL CREATE TABLE #tinitialcontact (eireferraldategg DATETIME, + startdategg DATETIME, + initialcontactlag =((DEXTRACT(startdategg)) - (DEXTRACT(eireferraldategg)))INTEGER) SSQL INSERT + INTO #tinitialcontact (eireferraldategg, + startdategg) + SELECT C.eireferraldate, A.startdate + …. Here I created the temporary table first, then inserting into it. not sure how to carry over the computed column… How do I tell RBASE to perform the calculations? (getting error messages even without including it, but perhaps this is because it needs that column). I tried changing field names. Also tried first querying the data on SQL server, and only afterwards adding the computed column. Could not get this to work. If someone could point out what I’m missing, that would be great. Thanks. Chaya From: [email protected] <mailto:[email protected]> [mailto:[email protected]] On Behalf Of Chaya Goldberg Sent: Friday, June 09, 2017 10:55 AM To: [email protected] <mailto:[email protected]> Subject: RE: [RBASE-L] - difference between two dates I believe I tried dextract and got an error, but I’ll try again. Thanks. From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of Buddy Walker Sent: Thursday, June 08, 2017 6:15 PM To: <mailto:[email protected]> [email protected] Subject: RE: [RBASE-L] - difference between two dates Chaya Here is a simple table structure using DEXTRACT as a computed column. Buddy ***********Tbl Struc Starts Here*************** CREATE TEMPORARY TABLE `MyTestTbl` + (`MyTestTblDate1` DATETIME, + `MyTestTblDate2` DATETIME, + `MyTestTblDiff` = ((DEXTRACT(MyTestTblDate2)) - (DEXTRACT(MyTestTblDate1))) INTEGER) **********Tbl Struc Ends Here*************** From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of Chaya Goldberg Sent: Thursday, June 8, 2017 5:46 PM To: <mailto:[email protected]> [email protected] Subject: RE: [RBASE-L] - difference between two dates Hi, I’m still having trouble with this. I’m doing the subtraction in a select statement (generating a computed column in a temporary table), and getting the difference in datetime format! As in ‘01/02/1900 12:00 AM’ instead of 2. Any ideas as to how I can display it as an integer? (besides for creating the temporary table first, then inserting the values. I tried that, but it’s having other unclear issues, so was wondering if there’s a direct way to do this.) Thanks. Chaya From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of Chaya Goldberg Sent: Thursday, June 08, 2017 11:36 AM To: <mailto:[email protected]> [email protected] Subject: RE: [RBASE-L] - difference between two dates Thank you, Albert and Thomas! From: <mailto:[email protected]> [email protected] [ <mailto:[email protected]> mailto:[email protected]] On Behalf Of Thomas J Hawley Sent: Wednesday, June 07, 2017 10:42 PM To: <mailto:[email protected]> [email protected] Subject: Re: [RBASE-L] - difference between two dates Keeping in mind that if you want the number of days between two dates, and deem the first and last days inclusive, you'll need to add 1 to the result. Tom Hawley On Wed, Jun 7, 2017 at 5:49 PM, Albert Berry <[email protected] <mailto:[email protected]> > wrote: R>SET VAR vDate2 DATE = 2017/06/06 R>SET VAR vDate1 DATE = 2017/06/01 R>SET VAR vDiff INTEGER = (.vDate2 - .vDate1) R>SHOW VAR vD% Variable = Value Type -------------------------------- ------------------------------ ------- vDir = -0- TEXT vDate2 = 2017/06/06 DATE vDate1 = 2017/06/01 DATE vDiff = 5 INTEGER On Jun 7, 2017, at 3:33 PM, Chaya Goldberg <[email protected] <mailto:[email protected]> > wrote: Hi All, I feel like this should be obvious, but can’t figure out how to do it. Is there a function that returns the difference between two dates in integer format, like datediff in SQL? Thanks in advance, Chaya -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to <mailto:[email protected]> [email protected]. For more options, visit <https://groups.google.com/d/optout> https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
