SQL: to_char alternative for Oracle AND SQL-Server

Question

I have some sql statements, which i am using for Oracle. This sql statements are used by a programm from me. I want to support Oracle and SQL-Server with my program without having different sql statements for Oracle and SQL-Server.

Which alternative can i use for the specific Oracle SQL-Statements:

• to_char(FIELDNAME, 'YYYY')
• to_char(FIELDNAME, 'YYYYMMDD')
• to_char(FIELDNAME, 'DD.MM.YYYY')

The sql statements have to work for Oracle and SQL-Server.

Answer 1

Even if at a first glance the SQL implementation from two different vendors looks similar, when working with real life enterprise applications you will stumble upon a large number of differences, and I am only talking about SQL, when comparing PL/SQL with T-SQL there is hardly any resemblance left.

When trying to reduce the usage of two databases to only common functionality, you will loose a lot of their power, you could as well use a txt file on the file system.

One elegant solution, as someone already suggested, would be to leave the columns in the database as DATE data type and extract your data in the application code that stands above the database, if any. For example, in Java, you will map your database DATE columns to java.sql.Date no matter if that date comes from Oracle or from SQL Server.

Still, if you want to get your formatted data from the database, you could create separate columns that hold the formatted date, for example :

FIELDNAME | FIELDNAME_YYYY | FIELDNAME_YYYYMMDD | FIELDNAME_DDMMYYYY

Answer 2

Finally i found a solution. Maybe its useful some other people too.

You can set the input format for a date...

Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY' SQL-Server: SET DATEFORMAT dmy

Answer 3

I don't think there are common functions to do what you want. Oracle supports the ANSI standard extract() function for extracting date parts. SQL Server has separate functions for YEAR(), MONTH(), and DAY(). Oracle uses TO_CHAR(); SQL Server uses CONVERT().

One option is to define the functions YEAR(), MONTH(), and DAY() in Oracle and then use string concatenation (via the CONCAT()) function to combine the data. Or, write specific functions in each database for what you want to do. Or, perhaps, someone has implemented TO_CHAR() in SQL Server and you can grab the code from the web.