Issues with date format

[Anonymous]

I have a table 'steps' with the following field headers

event	count	__KEY_2017-05-03

I want to extract the date in the third header and add it to another table. When extracting the date I get vDateRow == 2017-05-03, but it doesn't stay that way when loaded into the table. I've tried several different ways but I can't seem to get it right. In the table below you can see several of the methods I tried.

LET vDateRow = subfield(FieldName(3, 'steps'),'_',4);

[InputTable]:     

LOAD

Date($(vDateRow)) AS week_date,

    Date#($(vDateRow)) AS week_date_1,

    Date#($(vDateRow), 'YYYY-MM-DD') AS week_date_2,

     $(vDateRow) AS week_date_3,

     [event] as [event_community],

    [count] AS [count_community]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_steps]);

And this is the date format I get in the table. None of them are correct.

How do I get the correct date format?

[arvind_patil]

Hi Max,

With the help of PurgeChar you can skip extra fields from date.

Also first convert into date and then check.

Like Below:

Date(PurgeChar(Date Field,'Unwanted Char')) as NewDate;

Thanks,
Arvind Patil

[kusumanchir]

Hi Max,

After creating the variable it holds a string. So you may need to interpret and convert to date format.

Please try this way,

LET vDateRow = Date(Date#(subfield(FieldName(3, 'steps'),'_',4),'YYYY-MM-DD'),'YYYY-MM-DD');

[kenphamvn]

Hi

Please try

date(date#(KeepChar('__KEY_2017-05-03','0123456789'),'YYYYMMDD')) as NewDate

regards

[Anonymous]

Hi Rasly,

I've tried that as well and it gives me '2009' just as many other methods that I tried.

Thanks

[Anonymous]

Hi Arvind,

I'm not quite sure that I follow you. What characters do you suggest I remove and why?

Regards

[kusumanchir]

Hi Max,

I tried this way and I got as Date as 3rd may 2017,

LET vDateRow = Date(num(Date#(subfield(FieldName(3, 'steps'),'_',4),'YYYY-MM-DD')),'DD-MM-YYYY')

[pathiqvd]

Hi,

Try this,

datetest:

Load *,

year(__KEY_new_date) as __KEY_YEAR,

month(__KEY_new_date) as __KEY_MONTH;

load *,

date(date#(SubField(__KEY_Field,'_',4),'YYYY-MM-DD'),'YYYY-MM-DD') as __KEY_new_date

Inline [

__KEY_Field,Salesk

__KEY_2017-05-03,100

__KEY_2017-05-10,200

__KEY_2017-05-15,300

__KEY_2017-05-20,400

__KEY_2017-05-25,500

];

Regards,

[arvind_patil]

Hi Max,

Use Below:

=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')

Thanks,

Arvind Patil

[arvind_patil]

Hi Max,

Use Below:

=Date(Date#(PurgeChar('__KEY_2017-05-03','__KEY_'),'YYYY-MM-DD'),'DD-MMM-YYYY')

Thanks,

Arvind Patil