topic Convert date range text to Date in App Development

Convert date range text to Date

skzwane01 — Thu, 11 Aug 2022 19:09:04 GMT

Hi,

I need assistance, hoping to find a solution.

I would like to convert the below to text into a date format, that way I can be able to create a variable using the max function.

Text to convert = "2022-07-31 - 2022-08-06"

Kind Regards,

Re: Convert date range text to Date

luizcdepaula — Thu, 11 Aug 2022 20:08:34 GMT

Hi,

If the quotes are not part of the string, and the string character count is always the same, I would simply create two separate fields like below:

Temp:
Load
Left(Text,10) as Text1,
Right(Text,10) as Text2
From DataSource;

Final:
NoConcatenate
Load
MakeDate(Left(Text1,4),Mid(Text1,6,2),Right(Text1,2)) as Date1,
MakeDate(Left(Text2,4),Mid(Text2,6,2),Right(Text2,2)) as Date2
Resident Temp;

Drop Table Temp;

If the quotes are part of the string use PurgeChar to drop them first. Also, if the strings are not always the same character count, use Subfield to separate the date text.

I hope it helps.

Luiz

Re: Convert date range text to Date

BrunPierre — Thu, 11 Aug 2022 20:23:43 GMT

See attached!

Re: Convert date range text to Date

sidhiq91 — Fri, 12 Aug 2022 02:55:18 GMT

@skzwane01 Please see the below script that i have used in the back end:

NoConcatenate
Temp:
Load * Inline [
Date
2022-07-31 - 2022-08-06
];

NoConcatenate
Temp1:
Load Date(MakeDate(Left(Date1,4), mid(Date1,6,2), right(Date1,2)),'MM/DD/YYYY') as Date1,
Date(MakeDate(Left(Date2,4), mid(Date2,6,2), right(Date2,2)),'MM/DD/YYYY') as Date2;

Load *,
subfield(Date,' ',1) as Date1,
Right(Date,10) as Date2
Resident Temp;

Drop table Temp;

Exit Script;