Date to string in script

[jessica_webb]

I have a field in my data which will most often be a date, but will sometimes be a string value.

How can I get either value accepted?

If I load:

Field as DateorString

and the value is (e.g.) 22/05/2017 then QV turns it into 42877

But, if I load

Date(Field,  'DD/MM/YYYY') as DateorString

then whilst 22/05/2017 is shown correctly, any place where the value was actually a string doesn't get shown!

I've also tried:

Text(Field) as DateorString

but that still converts the date into a 5-digit value.

I'm only planning on using the date as a string anyway, but it needs to show in its correct format.

[tresesco]

I assume you have got dates in various formats in the source. If that is the case, best way to convert them to a unified format using below logic:

Date(Alt(Field, Date#(Field, 'PossibleFormat1'),Date#(Field, 'PossibleFormat2'), ..., Date#(Field, 'PossibleFormatN')) as Date

[jessica_webb]

Hi Tresesco,

Thanks for your response.

But on the contrary - I don't want them as a unified format!

Basically, the field allows the user (who inputs the data onto a spreadsheet) to define at what point in the year they are submitting data.

I want them to be able to give a date value if they choose, or a string value if they choose - and for it to display exactly as they have input it.

So one user might write '22/05/2017' and another user might write '8th Submission'.

So now, in the 'DateorString' field I would like to display:

22/05/2017

8th Submission

[prieper]

Did you load from Excel?

Thus in the underlying program, the date is not a text, but a numeric value and only displayed as text.

You may try to use the ALT-functionality in reading and converting the dates.

Peter

[tresesco · Accepted solution]

In that case Text(Field) should have worked. Sometimes, source data might just hold the numeric values (for proper date values) and that could led to show you five-digit values as you said. To handle that, you can try like:

If( Isnum(Field), Date(Field), text(Field)) as DateorString

[Kushal_Chawda]

may be you can try something like

=if(isnum(Field),date(Field,'DD/MM/YYYY'),

if(istext(Field),text(Field)))

[antoniotiman]

Hi Jessica,

QV recognises Field as Date Field (Dual)

Text(DateorString) -> 22/05/2017

Num(DateorString) -> 42877

I suggest You LOAD without change ed in Front End use always  Text(DateorString)

If field contains '8th Submission' You see 8th Submission.

Regards.

Antonio

[jessica_webb]

Thanks for all your responses.

I think it was the problem that Excel was storing it as numeric (which I hadn't realised!).

The solution of:

If( Isnum(Field), Date(Field), text(Field)) as DateorString

works perfectly.