what's the easiest method to write SQL statement that's suitable for mysql,oracle,and mssql database.
for instance after i attempt to execute this:
INSERT INTO "table_name ("date") VALUES (to_date('2011-02-11 16:48:08','yyyy-mm-dd hh24:mi:ss'));
it really works in oracle,but this question can give error: to_date isn't exist after i attempt to carry it out in MYSQL database.
but this follwoing SQL creates mysql and never operate in oracle:
INSERT INTO t_sys_log (date) VALUES ('2011-11-11');
You can as well help me to resolve date problem compatibility in mysql and oracle and let me know if there's a solution to make standard SQL statement works in rracle,mssql, and mysql ? Thanks
Yes, to_date won't be open to both databases, because it is a SQL extension that was only provided by Oracle.
It will get tougher the much deeper you decide to go. Date is less than exactly the same factor as Timestamp, so you will find yourself stuck where you will want Timestamps in certain databases and Dates in other people. The reason being you will find databases where Date only consists of a date, and never any connected hrs, minutes, and seconds.
MSSQL dates will also be susceptible to the platform's timezone rules, while other databases don't hold dates susceptible to timezone rules very much the same.
Ultimately, if you want results properly, you have to write an application layer which translates dates in to the formats needed through the underlying database, quitting around the "one SQL statement to complete all dates".
Try the ANSI standard DATE literal
Place INTO t_sys_log (date) VALUES ( date '2011-12-31')
insert into my_table (my_column) values (cast('2011-02-14' as date))
As noted elsewhere, MySQL, Oracle, and SQL Server each define the
DATE type in a different way. The database and connection locales could also modify the interpretation from the date string. Watch out for dragons.
I went into that same issue. Regrettably I could not find a great way to get it done. Rather I produced a plugable "assistant" class (it was in Java), which was set together with the database driver. It had techniques to produce areas of the SQL statement which were DBMS specific.