Database agnostic SQL for returning list for Date of birth stored as a Timestamp

Question

If I need to search on Date of birth which is stored without the hours and minutes but the date I have to search with includes hours and minutes what is the best way to return all rows where date is matched on only day, month and year

i.e.
Stored as 01-JAN-50 10.22.06.000000000
date selected 01-JAN-50 10.22.06.010101120

If I use the date with the hours and minutes the SQL will only return rows with the exact timestamp and not those for just the day, month and year.

The SQL needs to work on Oracle, SQLServer, MySQL and DB2.

Answer 1

There is no ANSI date format, nor string manipulation. Any code will run only in certain RDBMS's, if not just one.

However, if you're getting your selected date outside your RDBMS (say, PHP, Java, etc.), I'd suggest sanitizing it before sending it in the query; then you can compare string to string, which should work in almost all systems.

Answer 2

As any solution is going to require manipulation of date and datetime objects, then there will be no system-agnostic solution - each will have different functions for those objects.

The best solution, other than database abstraction, would be to round off the datetime object being used first, then only a generic SQL comparison clause is required which will be functional in all the listed DBs.

Answer 3

As others have pointed out, RDBMS's are very divergent when it comes to handling datetimes. If they actually followed the ANSI-92 standard, then the following should work:

SELECT
     <column list>
FROM
     <table name>
WHERE
     CAST(birth_date AS DATE) = CAST(search_date AS DATE)

The wouldn't be the most efficient way to do it though, since it would preclude using indexes on the date for most systems. The following might work for a fully ANSI-92 compliant database:

SELECT
     <column list>
FROM
     <table name>
WHERE
     birth_date >= CAST(CAST(search_date AS DATE) AS DATETIME) AND
     birth_date < CAST(CAST(search_date AS DATE) + 1 AS DATETIME)

Answer 4

Oracle's DATE type pre-dates the SQL standard version (as does Informix's), which makes this extremely difficult - if not impossible - to do in a DBMS-neutral fashion. Of course, there is a question of "why does the data representation chosen include time".

In standard SQL, the obvious technique would be to cast the TIMESTAMP to a DATE. We also don't have a clear explanation of the data you have to search with.

SELECT CAST(DateOfBirth AS DATE), ...other columns...
    FROM TheMysteryTable         -- Why do people hate giving tables names?
    WHERE CAST(DateOfBirth AS DATE) =
          CAST(TIMESTAMP '1950-01-01 10.22.06.010101120' AS DATE)

But that assumes that you write the 'date to search with' as a literal. If it is a host variable, then the type of the host variable should be DATE, not TIMESTAMP. And the DateOfBirth column should probably be a DATE, not a TIMESTAMP. You should not use TIMESTAMP unless the time part is relevant - it wastes storage and it wastes computation time.

Note that because of the casts, it is unlikely that the DBMS will be able to use any indexes or anything. If the types were sane, then the query would be simply:

SELECT DateOfBirth, ...other columns...
    FROM TheMysteryTable
    WHERE DateOfBirth = DATE '1950-01-01'

Answer 5

Use EXTRACT()