Level 2

Question

Date in a string field

Forum|Forum|1 year ago
June 28, 2024
3 replies
1738 views

Hi,

I need to use a date stored in a string field to do some queries.

In the specific case, I need to select contacts 10 days before a policy expiration date.

The expiration date is stored in a string field so I can't use the formula SubDays(,).

Is it possible to read the string field as a date? I use ToDateTime but doesn't work.

Thanks

E.

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

ParthaSarathy

Community Advisor

Hi @elisabettapi ,

Considering your string field has date format as MM/DD/YYYY, You can use DaysDiff() functionality instead of SubDays().

In Query, To fetch recipients who has policy expiration date after 10th day, add the condition as below

DaysDiff(ToDateTime(@your_string_field),DateOnly(GetDate()) ) EQUALS TO 10

~ ParthaSarathy S~ Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups

E

ElisabettaPiAuthor

Level 2

Hi,

thanks for your answer.

I tried your solution but it didn't work.

The error is: Invalid input syntax for type timestamp: " ".

The string date has this format "2024-08-27" without time, it is a text field.

I don't know if you have other suggestion for me.

Thanks a lot.

E.

ParthaSarathy

Community Advisor

DaysDiff(ToDateTime(@stringField),DateOnly(GetDate()) )

@elisabettapi , The Same worked fine for me with date format as 2024-08-27 .

~ ParthaSarathy S~ Click here to join ADOBE CAMPAIGN USER GROUP for Quarterly In-person | Hybrid | Virtual Meetups

S

SaisreeM

Hi @elisabettapi

Try to use "expiration date equal to DaysDiff( GetDate() -10 , 0)".

E

ElisabettaPiAuthor

Level 2

I tried but the error is the same specified in the comment above.

vraghav

Adobe Employee

@elisabettapi which RDBMS you are using for Campaign database or for the table where this string field is stored?

E

ElisabettaPiAuthor

Level 2

Hi, the field is in the Recipient table.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded