Highlighted

Month ahead SQL statement - Adobe Campaign

New Here ,
May 04, 2020

Copy link to clipboard

Copied

I am attempting to get a query that looks at an entire month ahead.

 

So for example.  We are sending out card expiry emails in this Month (May) but I want to query all those that have a subscription end date in June.

 

I have used:

on or after: AddMonths(ToDate( GetDate()) , 1)

AND before: AddMonths(ToDate( GetDate()) , 2)

 

But I think this will return June 4-July4.  All I want it June 1-June 30.  What is the correct SQ statement to use?

TOPICS
Database access

Views

591

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Month ahead SQL statement - Adobe Campaign

New Here ,
May 04, 2020

Copy link to clipboard

Copied

I am attempting to get a query that looks at an entire month ahead.

 

So for example.  We are sending out card expiry emails in this Month (May) but I want to query all those that have a subscription end date in June.

 

I have used:

on or after: AddMonths(ToDate( GetDate()) , 1)

AND before: AddMonths(ToDate( GetDate()) , 2)

 

But I think this will return June 4-July4.  All I want it June 1-June 30.  What is the correct SQ statement to use?

TOPICS
Database access

Views

592

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
May 04, 2020 0
LEGEND ,
May 04, 2020

Copy link to clipboard

Copied

Use ColdFusion to get the current month, then add one.  Get the last date of said month.  Pass them along to the query so that the SQL will be

 

WHERE date >= 01-{next month}-yyyy and date <= {last date}-{next month}-yyyy

 

HTH,

 

^ _ ^

 

UPDATE:  If you want to be more granular about it, and if the date includes the time:

WHERE date >= 01-{next month}-yyyy 00:00:00.0000 and date <= {last date}-{next month}-yyyy 23:59:59.9999

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
May 04, 2020 0