By data conversions, I mean mostly conversions from strings that are human readable to numbers and dates and back to strings. If you are one of those people who stores everything as strings (CHAR or VARCHAR) STOP IT. This is a BAD idea in both SQL Server and Oracle. But that's a subject for another post.
First of all, both databases will convert strings to numbers and dates implicitly if you don't include a conversion function. This works fine - until it gives you unexpected results or awful performance. I strongly recommend that you use a conversion function EVERY TIME you start with a number or date and want to show it on a report or screen in human readable form and EVERY TIME your get input from users and need to store it in its proper datatype. This way, the data will be converted in a predictable and efficient way. So what are the conversion functions?
ANSI SQL Conversion
Both Oracle and SQL Server are pretty compliant with the ANSI SQL standard, and so both support the ANSI SQL CAST function. The syntax of CAST is:CAST (expression AS datatype)
You can optionally include a size with the datatype, such as the length of a VARCHAR, or the precision and scale of a number. This works well in both databases for number conversions between floating point and integer datatypes, or from string to number - just understand that if the string doesn't contain a valid number, CAST will fail. SQL Server is nice in that it includes a TRY_CAST function that will return NULL for an invalid number rather than fail. Since CAST is ANSI standard, it is certainly preferred if you are writing code that may need to work on any database that meets the standard. But while CAST works for string to date and date to string conversions, the format of the string must be ANSI standard date format, which is the same as ISO_8601. Not so good when you need to convert to or from MM/DD/YYYY - your expression would need to substring the original date to put it in ANSI format.
Proprietary Conversion
So Oracle and SQL Server both have proprietary non-standard functions to convert between strings and dates more flexibly. SQL Server has:CONVERT (datatype, expression, style)
Just as with CAST, the datatype parameter can include size information. The style parameter is an integer that selects the data format of the input or output data from a table in the documentation - CAST and Convert. Now, I will admit that this list includes the formats that I use the most frequently for dates. For instance, style 1 is MM/DD/YY, and 101 is MM/DD/YYYY. But it doesn't include full month names in the selected natural language, such as January 1, 2018 in English or 1 Enero 2018 in Spanish.
Oracle has separate functions for converting a string to a date or a date to a string:
TO_DATE (expression, format, nlsparam)
TO_CHAR (expression, format, nlsparam)
The format parameter is optional and defaults to the default format set up by your DBA in your database. nlsparam is also optional and defaults to the NLS (natural language system) settings set by your DBA in your database. I almost never let format default, but almost always let nlsparam default. However, if you need to support languages other than English, this is what would let it appear as January for English speakers or Enero for Spanish. Documentation for TO_DATE, and TO_CHAR includes a list of formatting codes.
To me, the formats provided by Oracle are both more flexible and more clear. Which is easier to remember and easier to read:
CONVERT (VARCHAR(10), my_date, 101) - SQL Server
TO_CHAR (my_date, 'MM/DD/YYYY') - Oracle
Advantage - Oracle.
By the way, SQL Server's CONVERT function also works for numbers, but doesn't have a significant advantage over the CAST function for numbers. Oracle has TO_NUMBER and TO_CHAR functions for numbers with the same parameters as TO_DATE and TO_CHAR for dates, and more flexible formats for strings converted to and from numbers.
Advantage - Oracle.
No comments:
Post a Comment