How do I generate an SQL query that contains dates with Database Toolbox?

I would like to include dates in my SQL query. For example, I might want to select items from a table where the date in a column is later than a certain date.

Answer 1

[MathWorks Support Team]

Different databases require you to specify dates in different formats. Some databases accept dates in a standard string format ('19-Jan-2002') while others require special characters in the string to identify the string as a date.

In general, consult your database documentation for the proper format for dates in SQL queries. Below is some specific information for a couple of databases:

Microsoft Access:

With Microsoft Access, use a # character before and after the date string. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use the following:

curs = exec(conn,'select * from myDates where DateJoined > #03/05/2005#');

d = fetch(curs);

data = d.Data

Oracle:

With Oracle databases, the date string must be preceded by a data type identifier and enclosed in brackets. Below is an example, where "conn" is a database object in MATLAB as returned by the DATABASE command. The table name is "myDates", and the column name is "DateJoined". In order to select all records where the DateJoined field is after 3/5/05, use one of the following examples. The first statement uses the TIMESTAMP data type:

curs = exec(conn,'select * from myDates where DateJoined > {ts ''2005-03-05 00:00:00''}');

d = fetch(curs);

data = d.Data

The second statement uses the DATE data type:

curs = exec(conn,'select * from myDates where DateJoined > {d ''2005-03-05''}');

d = fetch(curs);

data = d.Data