How to Correctly Handle Dates in Queries Constraints

Problems in setting a date constraint on a parse query

Workaround:

queryResult._where.resultCeasesToBeValid = {'$lt' :{ "__type": "Date", "iso": today}}};

_where is a "private property" though and i wouldn't rely on it too much. Parse should fix this.

If you can [change the column type], i would suggest storing the date as a unix timestamp to avoid this kind of issues:

var timestamp = +new Date();
result.save({'resultCeasesToBeValid': timestamp});

Equals(=) vs. LIKE for date data type

Assuming LAST_TRANSACTION_DATE is a DATE column (or TIMESTAMP) then both version are very bad practice.

In both cases the DATE column will implicitly be converted to a character literal based on the current NLS settings. That means with different clients you will get different results.

When using date literals always use to_date() with(!) a format mask or use an ANSI date literal. That way you compare dates with dates not strings with strings. So for the equal comparison you should use:

LAST_TRANSACTION_DATE = to_date('30-JUL-07', 'dd-mon-yy')

Note that using 'MON' can still lead to errors with different NLS settings ('DEC' vs. 'DEZ' or 'MAR' vs. 'MRZ'). It is much less error prone using month numbers (and four digit years):

LAST_TRANSACTION_DATE = to_date('30-07-2007', 'dd-mm-yyyy')

or using an ANSI date literal

LAST_TRANSACTION_DATE = DATE '2007-07-30'

Now the reason why the above query is very likely to return nothing is that in Oracle DATE columns include the time as well. The above date literals implicitly contain the time 00:00. If the time in the table is different (e.g. 19:54) then of course the dates are not equal.

To workaround this problem you have different options:

use trunc() on the table column to "normalize" the time to 00:00
trunc(LAST_TRANSACTION_DATE) = DATE '2007-07-30
this will however prevent the usage of an index defined on LAST_TRANSACTION_DATE
use between

LAST_TRANSACTION_DATE between to_date('2007-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2007-07-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

The performance problem of the first solution could be worked around by creating an index on trunc(LAST_TRANSACTION_DATE) which could be used by that expression. But the expression LAST_TRANSACTION_DATE = '30-JUL-07' prevents an index usage as well because internally it's processed as to_char(LAST_TRANSACTION_DATE) = '30-JUL-07'

The important things to remember:

Never, ever rely on implicit data type conversion. It will give you problems at some point. Always compare the correct data types
Oracle DATE columns always contain a time which is part of the comparison rules.

How to create a check condition that checks for if date is a specific date then income has to be x?

You can't create a constraint on existing tables when some entries don't satisfy the constraint's conditions.
In case you really need to check all those conditions, you have to update the already existing entries before creating the constraint to make sure all rows satisfy the conditions.
Or if this is not intended, you need to create a before insert trigger instead of a constraint that will check if new entries are allowed or not. If you create such a trigger, you should also check if before insert is sufficient or also before update should be done.
In your situation, it seems the conditions of your constraint are incorrect. Likely the correct syntax (according to your description) will be:

ALTER TABLE Employees
ADD CONSTRAINT nyIncome
CHECK (payDate = '2010-01-01' AND (income > 100000 OR income IS NULL));

If you're not sure about the correct conditions for your constraint, execute a select first with the exact conditions as where clause that you want to check by your constraint and see if the result is your entire table. Or revert the where clause and check if the result is empty.

Update because you edited your question: You can't create such a constraint because there are also entries with another date in your table. You likely will need a trigger that forbids to insert/update entries having an income <= 100000 and the date '2010-01-01'.
Or just create a precise where clause in your queries instead of a trigger if these restrictions should be applied in queries only.

Hibernate Criteria for Dates

Why do you use Restrictions.like(...)?

You should use Restrictions.eq(...).

Note you can also use .le, .lt, .ge, .gt on date objects as comparison operators. LIKE operator is not appropriate for this case since LIKE is useful when you want to match results according to partial content of a column.
Please see http://www.sql-tutorial.net/SQL-LIKE.asp for the reference.

For example if you have a name column with some people's full name, you can do where name like 'robert %' so that you will return all entries with name starting with 'robert ' (% can replace any character).

In your case you know the full content of the date you're trying to match so you shouldn't use LIKE but equality. I guess Hibernate doesn't give you any exception in this case, but anyway you will probably have the same problem with the Restrictions.eq(...).

Your date object you got with the code:

SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-YYYY");
String myDate = "17-04-2011";
Date date = formatter.parse(myDate);

This date object is equals to the 17-04-2011 at 0h, 0 minutes, 0 seconds and 0 nanoseconds.

This means that your entries in database must have exactly that date. What i mean is that if your database entry has a date "17-April-2011 19:20:23.707000000", then it won't be retrieved because you just ask for that date: "17-April-2011 00:00:00.0000000000".

If you want to retrieve all entries of your database from a given day, you will have to use the following code:

    SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-YYYY");
    String myDate = "17-04-2011";
    // Create date 17-04-2011 - 00h00
    Date minDate = formatter.parse(myDate);
    // Create date 18-04-2011 - 00h00 
    // -> We take the 1st date and add it 1 day in millisecond thanks to a useful and not so known class
    Date maxDate = new Date(minDate.getTime() + TimeUnit.DAYS.toMillis(1));
    Conjunction and = Restrictions.conjunction();
    // The order date must be >= 17-04-2011 - 00h00
    and.add( Restrictions.ge("orderDate", minDate) );
    // And the order date must be < 18-04-2011 - 00h00
    and.add( Restrictions.lt("orderDate", maxDate) );

How to handle date type in SQLite?

Bar the rowid column or an alias of the rowid column, any type of value can be stored in an type of column. That is the type of column does not restrict/constrain the data that can be stored.

p.s. there is no DATE type rather due to SQLite's flexibility DATE actually has a type (type affinity) of NUMERIC (not that that matters that much). You might find Datatypes In SQLite Version 3 an interesting read or perhaps this How flexible/restricive are SQLite column types?.
the rowid and, therefore an alias thereof, column MUST be an integer. Although typically you allow SQLite to assign the value.

You should either check the data programatically or alternately use a CHECK constraint when defining the column in the CREATE TABLE SQL.

A CHECK constraint may be attached to a column definition or specified
as a table constraint. In practice it makes no difference. Each time a
new row is inserted into the table or an existing row is updated, the
expression associated with each CHECK constraint is evaluated and cast
to a NUMERIC value in the same way as a CAST expression. If the result
is zero (integer value 0 or real value 0.0), then a constraint
violation has occurred. If the CHECK expression evaluates to NULL, or
any other non-zero value, it is not a constraint violation. The
expression of a CHECK constraint may not contain a subquery.

SQL As Understood By SQLite - CREATE TABLE

Example

Consider the following code :-

DROP TABLE IF EXISTS mychecktable ;
CREATE TABLE IF NOT EXISTS mychecktable (mycolumn BLOB CHECK(substr(mycolumn,3,1) = '-'));
INSERT INTO mychecktable VALUES('14-03-1900');
INSERT INTO mychecktable VALUES('1900-03-14'); -- ouch 3rd char not -

The is will result in :-

DROP TABLE IF EXISTS mychecktable
> OK
> Time: 0.187s

CREATE TABLE IF NOT EXISTS mychecktable (mycolumn BLOB CHECK(substr(mycolumn,3,1) = '-'))
> OK
> Time: 0.084s

INSERT INTO mychecktable VALUES('14-03-1900')
> Affected rows: 1
> Time: 0.206s

INSERT INTO mychecktable VALUES('1900-03-14')
> CHECK constraint failed: mychecktable
> Time: 0s

i.e. the first insert is successful, the second insert fails.