SQL Date Issues

[samwoo]

Hello,

When it comes to querying and using functions related to dates (such as CURDATE() or h_datelogged) it doesnt work

Few examples:

DATE_FORMAT(h_datelogged,'%d') does not produce the day... in fact nothing in this function works, it just produces a blank field

CURDATE() doesn't work

DATE(h_datelogged) produces an empty field

DAY(h_datelogged) produces an empty field.

We have tried different variants but the all produce empty fields. Only when we query h_datelogged it brings back a value. And i think it does the same thing for other date fields too.

Is there any pure documentation for the SQL that can be used in Hornbill?

Thanks,

Samuel

 

[Everton1878]

I've been having the same problem, I want to create a measure to show the number of calls per team that were logged over 90 day ago and are still not resolved

GETDATE() and CURDATE() were giving me access denied messages when running in the database query tool

[Guest]

Hi Samuel, Pete

Depending on where you use these functions, they do work. For example, if you use them as part of a WHERE clause in a List Of Counters widget, they should be usable. Though I am aware that they same queries do not always work in Database Direct - I'll check with the developers and get back to you. 

Kind Regards,

Bob

[Gerry]

Hi Samuel,

This is to do with the way we handle SQL results in a more generic way.  Our system does not understand "DATE()" as being a column name. If you want to include such values then you need to use the AS keyword to name the return params.  For example...
 

SELECT NOW() AS time_now;

From a pure SQL point of view, naming your return params where there is ambiguity is a good habit to get into, makes everything clear and concise. 

Gerry

[samwoo]

Hi @Gerry,

Brilliant, i don't generally use MySQL but I can definitely see why we need to give each column a name as i've come across that in other SQL where the column becomes too long.

All resolved from my side - @Everton1878 does Gerry's advice resolve it for you?

Thanks,

Samuel

[Everton1878]

3 minutes ago, samwoo said:

Hi @Gerry,

Brilliant, i don't generally use MySQL but I can definitely see why we need to give each column a name as i've come across that in other SQL where the column becomes too long.

All resolved from my side - @Everton1878 does Gerry's advice resolve it for you?

Thanks,

Samuel

That should resolve it for me too

[Everton1878]

I think I'm sorted too now @Gerry and @samwoo