SQL database agnostic for return list for date of birth stored as timestamp
If I need to search for a date of birth that is stored without hours and minutes, but the date I have to perform includes hours and minutes, what is the best way to return all rows where the date only matches the day, month and year
i.e.
Stored as 01-JAN-50 10.22.06.000000000
selected date 01-JAN-50 10.22.06.010101120
If I use a date with hours and minutes, SQL will only return rows with an exact timestamp, not just the day, month, and year.
SQL should work with Oracle, SQLServer, MySQL and DB2.
a source to share
The Oracle DATE type precedes the standard version of SQL (as does Informix), making it extremely difficult — if not impossible — to do in database neutral mode. Of course, the question is, "Why does the selected data representation include time?"
In standard SQL, the obvious technique is to include TIMESTAMP in DATE. We also don't have a clear explanation of the data you should be looking for.
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 this assumes that you are writing 'date to search with' as a literal. If it is a host variable, then the type of the host variable must be DATE, not TIMESTAMP. The DateOfBirth column must be DATE, not TIMESTAMP. You shouldn't use TIMESTAMP unless the timing part is relevant - it destroys storage and wastes computation time.
Note that due to the throws, it is unlikely that the DBMS will be able to use any indexes or whatever. If the types were normal, then the request would be simple:
SELECT DateOfBirth, ...other columns...
FROM TheMysteryTable
WHERE DateOfBirth = DATE '1950-01-01'
a source to share
Since any solution would require manipulating dates and datetime objects, then there would be no system-agnostic solution - each one would have different functionality for those objects.
Better solution other than database abstraction would be to round up the datetime object to be used first, then only a generic SQL comparison clause is required that will function across all the databases listed.
a source to share
There is no ANSI date format nor string manipulation. Any code will only work in certain DBMS, if not one.
However, if you are getting the selected date outside of your RDBMS (say PHP, Java, etc.), I suggest sanitizing it before submitting it to the request; then you can compare string to string, which should work on almost all systems.
a source to share
As others have pointed out, RDBMSs are very at odds when it comes to data processing. If they did follow 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)
This is not the most efficient way to do this, as it will prevent indexes from being used per day on 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)
a source to share