Running a query to send email subscribers a +X day email. FIRST_PROMO_SUBSCRIBE_DATE is coming from Oracle which they say is not a compatible format from Salesforce SQL so I have;
select * from PROMO_SUBSCRIBERS
where
(ORDER_ENGAGEMENT_LAST_DT > dateadd(day,-335,CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
or ORDER_ENGAGEMENT_LAST_DT is null)
and
(ORDER_LAST_DT > dateadd(day,-1,CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
or order_last_dt is null)
Are the parentheses correct?
You should just be able to cast the Oracle dates to date. Casting to date will also strip the time portion off of the datetime fields in T-SQL.
MS SQL Server 2008 Schema Setup:
CREATE TABLE promo_subscribers
(
emailaddress varchar(255)
, ORDER_ENGAGEMENT_LAST_DT varchar(15)
, ORDER_LAST_DT varchar(15)
);
INSERT INTO promo_subscribers
(emailaddress, ORDER_ENGAGEMENT_LAST_DT, ORDER_LAST_DT)
VALUES
('test@example.com', '01-APR-98', '01-APR-16'),
('test@example.com', '01-MAY-98', '06-APR-16')
Query 1:
select
emailaddress
, order_engagement_last_dt
, cast(order_engagement_last_dt as date) datecast1
, order_last_dt
, cast(order_last_dt as date) datecast2
, dateadd(day,-335, cast(getDate() as date)) datecast3
, dateadd(day,-1, cast(getDate() as date)) datecast4
from PROMO_SUBSCRIBERS
| emailaddress | order_engagement_last_dt | datecast1 | order_last_dt | datecast2 | datecast3 | datecast4 |
|------------------|--------------------------|------------|---------------|------------|------------|------------|
| test@example.com | 01-APR-98 | 1998-04-01 | 01-APR-16 | 2016-04-01 | 2015-05-08 | 2016-04-06 |
| test@example.com | 01-MAY-98 | 1998-05-01 | 06-APR-16 | 2016-04-06 | 2015-05-08 | 2016-04-06 |
