Date in a string field

[ElisabettaPi]

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.

[ParthaSarathy]

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

[ElisabettaPi]

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]

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

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

[ElisabettaPi]

I don't know why didn't work for me

[SaisreeM]

Hi @ElisabettaPi 

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

[ElisabettaPi]

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

[vraghav]

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

[ElisabettaPi]

Hi, the field is in the Recipient table.