How to isolate a date between quotes in a column ?

[bouderbc]

How to isolate a date between quotes in a column  ?

All the date values of my column are between quotes , Telle me How to isolate the value between quotes

Example: Column: Date

Fields Text Format ' DD-MM-YYYY'  into  Result  DD-MM-YYY

' DD-MM-YYYY'  into DD-MM-YYY

Regards

Ali

[vvira1316]

Hi Ali,

Have you tried

Date#(TextDate, 'DD-MM-YYYY') this should result in your expecte output

Date#('31-12-2016', 'DD-MM-YYYY') as 31st of Dec, 2016 will be seen as 31-12-2016

Not sure ' DD-MM-YYYY'  space before DD, if so you can remove space using trim function.

If this is what you are not expecting then please share sample data/app.

BR,

Vijay

[bouderbc]

Hi Vijay,

i have already tried Date#(TextDate, 'DD-MM-YYYY') but it doesn't work and the subfield function without success

i share an excel sample , so all the fields of the two columns are between quotes .

Please tell me if you have a solution

Thank you

Regards

Ali

[rwunderlich · Accepted solution]

TextBetween(Datefield, chr(39), chr(39))

or

SubField(DateField, chr(39), 1)

-Rob

[vvira1316]

Hi Ali,

As Rob mentioned, yes you can use Chr(39) for Single Quote. You can use PurgeChar, SubField, TextBetween, Date# for formatting. You have date in two formats (DD/MM/YYYY and DD-MM-YYYY) in the file you have provided so not sure if your source data is clean and consistent. In either case you can handle it in loading or in chart.

NoConcatenate
Tablemodele:
LOAD Date#(PurgeChar([Date creation], Chr(39))) as [Date creation]
,Date#(PurgeChar([Date Extract], Chr(39))) as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(ooxml, embedded labels, table is [Table modele])

BR,

Vijay

.

[vvira1316]

here it is

NoConcatenate
Tablemodele:
LOAD Date(Date#(PurgeChar([Date creation], Chr(39)), 'DD-MM-YYYY'), 'DD-MM-YYYY') as [Date creation]
,Date(Date#(PurgeChar([Date Extract], Chr(39)), 'DD-MM-YYYY'), 'DD-MM-YYYY') as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(ooxml, embedded labels, table is [Table modele])
where FindOneOf([Date Extract], '-');
Concatenate
LOAD Date(Date#(PurgeChar([Date creation], Chr(39)), 'DD/MM/YYYY'), 'DD-MM-YYYY') as [Date creation]
,Date(Date#(PurgeChar([Date Extract], Chr(39)), 'DD/MM/YYYY'), 'DD-MM-YYYY') as [Date Extract]
FROM
[..\Data\Tablemodele.xlsx]
(ooxml, embedded labels, table is [Table modele])
where FindOneOf([Date Extract], '/');

[bouderbc]

SubField(DateField, chr(39), 1) is the right solution !!! Thank you Rob

[bouderbc]

Correct Thank you ! u're Great

[bouderbc]

LOAD SubField([Date Extract],chr(39),1) as [Date Extract] ,

     SubField([Date creation], chr(39),1) as [Date creation],

From..