topic Re: Date Format in App Development

Date Format

SilviyaK — Thu, 19 Oct 2023 14:08:20 GMT

Hi all,

I have a text field that I need to format as a date, but it looks like this:

1-15 March 2023

1-5 April 2024

etc...

How can I format it to a date field so it can also take into account the dates in between the given range? For example for the first one it is all days between 1st of March and 15th of March.

Thanks!

Re: Date Format

maheshkuttappa — Thu, 19 Oct 2023 15:56:25 GMT

Try this code.

tDate:
Load * Inline [
Date
1-15 March 2023
1-5 April 2024
];

Date:
Load
Date as DateID,
Date(Date#(left(Date,Index(Date,'-',-1)-1)& Mid(Date, Index(Date,' ')),'DD MMMM YYYY'),'DD-MM-YYYY')as StartDate,
Date(Date#(Right(Date, len(Date)-Index(Date,'-',-1)),'DD MMMM YYYY'),'DD-MM-YYYY') as EndDate

Resident tDate;

Drop Table tDate;

ContinuesDate:
Load
DateID,
Date(StartDate + IterNo()-1,'DD-MM-YYYY') as Date

Resident Date While StartDate + IterNo()-1 <= EndDate;

Re: Date Format

Sivapriya_d — Thu, 19 Oct 2023 16:45:29 GMT

Hi,

Hope this helps.

Temp:
Load
PurgeChar(Date,'- ') as Date_ref,
Date#(SubField(Date,'-',1)&mid(Date,Index(Date,' ')),'DD MMMM YYYY') as StartDate,
Date#(SubField(Date,'-',2),'DD MMMM YYYY') as EndDate;
Load * Inline [
Date
1-15 March 2023
1-5 April 2024
];

DateTable:
Load
Date_ref,
Date(StartDate + IterNo()-1) as Date
Resident Temp While StartDate + IterNo()-1 <= EndDate;

Re: Date Format

SilviyaK — Fri, 20 Oct 2023 07:09:43 GMT

Thanks! This worked perfectly with my data!