0

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

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.

SQL Fiddle

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

Results:

|     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 |
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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