Dates compare directly between components and SQL tables?

Archived Topics
1

I have a calendar component and want to compare a date returned from it to one in a database table. I tried checking if <= to current date, but that does not seem to work.

2

Ron,

Give us a little more information. Where is this comparison taking place? In a SQL query, an expression, a script? When you say it ‘does not seem to work’, what do you mean?

3

Ron,

Dates in the database have a specific format, and the calendar component could have one of many formats. What database are you using? If it’s MySQL, dates are stored as ‘yyyy-mm-dd hh:mm:ss’ but you could use the DATE_FORMAT() function to set it up any way you want.

4

To clarify, all databases that I know of (not just MySQL) accept dates in the format ‘yyyy-MM-dd HH:mm:ss’ (careful of the capitalization of those format strings).

Conveniently, when you attach Date objects into SQL queries in FactoryPMI, this is the format that they are inserted in, so date formatting is rarely needed. For instance you should be able to do this in a query binding:

SELECT something FROM table WHERE t_stamp <= '{Root Container.Calendar.Date}'
5

I am doing the following:

If({Root Container.Table.data}[{Root Container.Container.TankNum},3] <= {Root Container.Calendar.date},"EMPTY","FULL")

The error I get when using the above command isType mismatch in operation ‘Less Than or Equal’: expacted ‘Number’,found ‘Object’

This is when binding a dynamic property to the value in a table.

6

Ah, I see what you’re doing. Date comparison in the expression language.
Note that this was just added in version 3.1.5, so make sure you’re using that version. All you need is a cast to convince the expression language that the data inside the DataSet is indeed a Date, like so:

if( toDate({Root Container.Table.data}[{Root Container.Container.TankNum},3]) <= {Root Container.Calendar.date}, "EMPTY","FULL")

7

That did it. I just had to add a test for a NULL date and now it is working. Thanks.