2

I'm doing a manual INSERT where I have to format a DateTime value in C#. The resulting string is put into a a SQL query. I need a cross-platform format that can be hendled by Oracle, SQL Server and Firebird.

Example:

With yyyy-mm-dd you save the date correctly without relying on the language of the database.

I need the format not to uncouple from language but from vendor quirks.

pid
  • 11,472
  • 6
  • 34
  • 63
mtsys
  • 249
  • 1
  • 4
  • 15
  • 3
    This is one of the many problems that go away when you use query parameters. – Dan Bracuk Sep 24 '14 at 19:40
  • @DanBracuk I can not use parameters, because I have to reduce the maximum processing – mtsys Sep 24 '14 at 19:43
  • 5
    Parameters generally decrease the processing time because Oracle and SQL Server will cache the INSERT statements if they're parameterized, whereas they'll recompile the statements each time if they're not. I don't know about Firebird. As for a common date format, there really isn't a reliable one. SQL Server is OK with `'2014-09-24'`, but Oracle wants the ANSI standard `DATE '2014-09-24'`. It will take `'2014-09-24'` if that's set as the default date format for the session (instead of the "stock" `dd-mon-rr` format), but relying on the session date format is extremely risky. – Ed Gibbs Sep 24 '14 at 20:06
  • Not using bind parameters for Oracle (for OLTP ) is a huge performance and scalability killer. – OldProgrammer Sep 24 '14 at 20:14
  • 1
    Firebird only caches the instance of the prepared statement handle (it doesn't reuse the plan of the statement for multiple handles), and it supports the SQL standard `DATE'yyyy-MM-dd'` format literal (it will usually also work without the prefix). – Mark Rotteveel Sep 25 '14 at 08:24
  • `DATE'yyyy-MM-dd'` should work. Other tricks that come into my mind (encoding a date from year/month/day on the fly or some sly conversions) will only add incompatibility due to DB-specific syntax. But if your conditions allow I'd recommend using parameters anyway. String datetime representation makes sense only when generating a transferrable SQL script. – Fr0sT Oct 30 '14 at 12:54

0 Answers0