Date# takes 1-2 parameters error

[khaycock]

I am getting the above error for the below script and I have no idea why. The IF statement worked before I added the date part and the date part works out of the IF statement?

if(Date(Date#(Left(DateField, 10), 'YYYY-MM-DD'), 'DD/MM/YYYY')=0, 'N/A',
if(Date(Date#(Left(DateField, 10)<= date(today(),'DD/MM/YYYY')-15,'Not Reporting',
'Control Met')) AS DateFieldOLD,

[marcus_sommer]

Try this:

if(Date(Date#(Left(DateField, 10), 'YYYY-MM-DD'), 'DD/MM/YYYY')=0, 'N/A',
if(Date(Date#(Left(DateField, 10), 'YYYY-MM-DD')<= date(today(),'DD/MM/YYYY')-15,'Not Reporting',
'Control Met')) AS DateFieldOLD,

- Marcus

[khaycock]

That solves the second issue, I'm still getting an error on the first IF statement 😞 

[marcus_sommer]

It seems that your date#() conversion failed and then returned date() NULL. Try it with:

if(isnum(Date#(Left(DateField, 10), 'YYYY-MM-DD'))=0, 'N/A',
if(Date(Date#(Left(DateField, 10), 'YYYY-MM-DD')<= date(today(),'DD/MM/YYYY')-15,'Not Reporting',
'Control Met')) AS DateFieldOLD,

whereby I think it would be better to transform (and if it's a timestamp to split into a date- and a time-field) the field at least one step before to simplify all the following matches/calculations.

- Marcus

[khaycock]

Unfortunately I have the same error with this one, however the error is now saying that Date takes 1-2 parameters rather than Date#..

I do transform it beforehand but it all gets a bit complicated as there is a lot going on. This is what I had initially and it worked:

date(floor(date#(Control,'YYYY-MM-DD hh:mm:ssZ')),'DD/MM/YYYY') AS ControlDate,
IF(len(trim(date(floor(date#(Control,'YYYY-MM-DD hh:mm:ssZ')),'DD/MM/YYYY')))=0,'N/A',
if(date(floor(date#(ControlTenable,'YYYY-MM-DD hh:mm:ssZ')),'DD/MM/YYYY')<= date(today(),'DD/MM/YYYY')-15,'Not Reporting', 'Control Met')) AS ReportingOLD,

However, the date format randomly changed to 'YYYY-MM-DDThh:mm:ss' and I can't get the above to work with that so I tried the Left instead. 

If you there is an easier way to get the format to amend to DD/MM/YYYY rather than the left I guess that might work better?

[marcus_sommer]

I think the Z within date#(Control,'YYYY-MM-DD hh:mm:ssZ') won't be interpreted like you want. If it's really there I think I would remove it with replace() or maybe a keepchar/purgechar(). Beside this if there are multiple formattings you could use alt() for it, maybe with something like this:

alt(timestamp#(purgechar(Control, 'Z'),'YYYY-MM-DD hh:mm:ss'),
       timestamp#(Control,'YYYY/MM/DD hh:mm:ss'), 'no date available')

- Marcus

[khaycock]

Can I use the replace function but also amend the date format to be DD-MM-YYYY?

[marcus_sommer]

Not within the formatting-functions date() but in the converting-functions like date#().

- Marcus

[khaycock]

I think what I don't understand is why it worked fine when there was a random Z in the date like this:

date(floor(date#(Control,'YYYY-MM-DD hh:mm:ssZ')),'DD/MM/YYYY') AS ControlDate,

But doesn't work when there is a random T in the middle like this:

date(floor(date#(Control,'YYYY-MM-DDThh:mm:ss')),'DD/MM/YYYY') AS ControlDate,

Does it just throw it off track because it can't separate between the date and the time?

It's annoying because I have many controls that all have the Z date and only one with the T date and I don't want to change the logic for all 😞