0

Relating to my question related to finding a date X% between two dates using SQL, I'm having a challenge resolving two other similar things:

  1. How to identify the % of time between two dates. Here's what I tried, which didn't seem to work. I tried GETDATE() instead of NOW() and that didn't work either:
    SELECT  
    ((DATEDIFF(NOW() - Moveindate__c) / (Moveoutdate__c - Moveindate__c) ) *100 as [tenancy_percentage]  
    FROM X
  1. How to identify only records where today is between two dates in a WHERE clause. Here's what I tried, which didn't work:
    WHERE Request.Moveoutdate__c BETWEEN (Request.Moveindate__c) AND (Request.Moveoutdate__c)
1

"Now()" is an AMPScript function, rather than an SQL function and logically Request.Moveoutdate__c will always be between Request.Moveindate__c and Request.Moveoutdate__c.

It sounds like you're looking for something along these lines:

SELECT
  Id,
  DATEDIFF(day,Moveindate__c,GETDATE()) / DATEDIFF(day,Moveindate__c,Moveoutdate__c) * 100 AS PercentComplete
FROM 
  Request
WHERE 
  GETDATE() BETWEEN Moveindate__c AND Moveoutdate__c
| improve this answer | |
  • Thanks @Macca. The query validated and it populated with records as expected, however the PercentageComplete field returned with a 0 value. I've tried setting the field type to Percentage, Number, and Text. Is there a step missing in defining the field type in the query? – Greg Beazley May 24 at 15:49
  • I'd probably create as something like Decimal(18,2) for your field, unless you need whole numbers and use the SQL Convert function to set your PercentComplete field to the same data type - Decimal(18,2). Also, make sure that the field names in your Data Extension match the aliases for field names in the query - they must match exactly. – Macca May 26 at 4:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.