date picker doesnt recognize all date fields

[ioannagr]

Hello all.

I have two date fields in my qvd in two different tables, in the form of date(floor([date field timestamp 1],'DD/MM/YYYY')) as datefield1 and date(floor([date field timestamp 2],'DD/MM/YYYY')) as datefield2  and also master calendars for them both.

Now i was asked to use the date picker extension but only recognizes datefield1 and datefield1MonthYear (from master calendar).  Datefield2 is ignored completely.

what should i do? I tried changing the SET variable, i tried TAG field datefield2 as '$date', none works.

[Or]

Have you confirmed that the field in question only contains valid dates? Note that to be recognized as a date, it needs to be a dual-type field with underlying numeric values, so you should be able to get results for every row of data using both Num([timestamp 2]) and text([timestamp 2]) for non-null values.

[ioannagr]

Hi and thank you @Or  🙂 So what is exactly the cross check i should do for timestamp2?

Check for null values and see if this field gives results for both Num([timestamp 2]) and text([timestamp 2]) ?

And next how should i treat it for date picker to work?

[Or]

I would try, in script (hopefully syntax is correct, I can't test)

If(len(num([timestamp 2]))=0 AND NOT IsNull([timestamp 2]),'Not a number')  as IsThisANumber

If any values of 'Not a number' are returned, those are probably not valid dates since they lack numeric representation.

There's probably an easier way to do this, but I think this one would work...

[ioannagr]

@Or , all timestamp2  are '-' for this command!

[Or]

That suggests one of two things:

1) I made a mistake in the formula

2) They are not dates

Try explicitly formatting them as dates / converting them to dates using date() or date#() (or the respective timestamp function if that's needed) and see if that helps.

[ioannagr]

But @Or , i though this in my script that i did : "date(floor([date field timestamp 1],'DD/MM/YYYY')) as datefield1 and date(floor([date field timestamp 2],'DD/MM/YYYY')) as datefield2"  that  i mentioned in my first post , declares that datefield2 is a date.... and looks like it in a filter, floored and all.

I'm confuuuuuuuuuuuuuuuuuused

[Or]

Indeed, if they are floored correctly you would expect them to be legally numeric.. and thinking about it, there are easier ways to check if it's a legal date - just see if it responds correctly to any date-specific command such as Year(datefield2).

If it responds correctly to date-specific functions, and you've tagged it as $date (sometimes necessary, particularly if your date range might include dates in the distant past or distant future), and the calendar still doesn't pick it up, I'm stumped.