Oracle’s TO_CHAR function converts datetime values to strings using any specified format; its equivalent in SQL Server can be found with CONVERT or CAST functions.

PL/SQL TO_CHAR is an efficient conversion function with three parameters – expr, format and nls_language – that has become widely utilized across organizations. Additionally, this function can also convert dates and intervals to the varchar2 data type.

The TO_CHAR function

Oracle’s TO_CHAR function converts datetime values to string in accordance with an input format, similar to CAST and VARCHAR2. However, TO_CHAR is more powerful as it can convert any datetime type or interval data type to character data type.

The Oracle TO_CHAR function is an inbuilt SQL function available across all versions of Oracle database. It can be used in select lists, WHERE clauses, START WITH and CONNECT BY clauses as well as HAVING clauses.

Convert a datetime or interval data type to character data type using user-specified formats. Useful for keeping an application running smoothly by maintaining continuity from data type conversion.

Oracle TO_CHAR can also be used to convert the result of a SELECT query into a date compatible with SQL statements. The TO_CHAR function takes an expression and date format, as well as an nls_language argument, as arguments.

This function works similarly to TO_DATE in that it accepts an expression and date or interval format as input before converting it to a string value. It’s useful for creating calendars or printing values in formats compatible with SQL statements.

TO_CHAR is useful when creating dates in the year 2000 or returning salaries in specific formats for particular years.

TO_CHAR offers several formatting suffixes, such as DD.FF and YYYY, that can be added to datetime format elements to alter their output format. DD stands for the first two digits of year; FF represents fractional seconds; while YYYY refers to three or four digits.

TO_CHAR replaces any significant digits to the left of decimal place that exceed the specifications in datetime format with pound signs (#), and stores the resultant bytes as one single character value in its output buffer.

The CONVERT function

The CONVERT function in SQL Server converts datetime values to strings with any specific format specified, making this an invaluable function when your database needs to display datetime values as strings such as in YYYY/MM/DD format or when needing to convert one into an integer value.

To successfully migrate SQL data between systems, the types of values in your database must match those of the target objects or systems so you can perform desired operations on them.

Oracle inbuilt functions include type conversion functions that can help facilitate this task, either implicitly implemented by Oracle or explicitly defined and implemented by a programmer.

When upgrading an Oracle database to use a platform with different numeric encoding, it is simple and fast to use TO_CHAR and CAST functions to convert all existing values to match their new character set.

This will enable you to move data between platforms without the need to re-define tables and data, making this strategy especially helpful when transitioning from an old Oracle database to a new one.

Furthermore, certain data types can be converted to other formats by replacing one value with the corresponding one; examples of this are TIMESTAMP and INTERVAL DAY TO SECOND data types.

However, to convert other data types such as numbers and strings you will require an explicit type conversion function such as CONVERT in SQL Server or CAST in Oracle.

The CAST function in SQL Server provides more robust type conversion than TO_CHAR and TO_DATE functions, offering greater control and flexibility in specifying data type conversion as well as length of converted value. You can set styles that define how it should translate or format data type.

The CAST function

The CAST function in SQL Server allows users to convert datetime values to strings using any format specified. It accepts datetime value and format as its first arguments, followed by VARCHAR as its second argument and an optional style argument.

SQL Server TO_CHAR is similar to its Oracle counterpart in that it uses a format specifier to return a formatted date value, but differs in using a datetime style that defines how dates should appear as strings instead of using YYYY and MM format specifiers like TO_CHAR does.

TO_CHAR is an optional function in PL/SQL that converts DATE, TIMESTAMP, and TIME STAMP WITH TIME ZONE data types into the data type value varchar2. Optional arguments of TO_CHAR include date_format and nls_language.

The TO_CHAR function in Oracle and other databases is very similar to CAST in terms of functionality; the primary difference being that TO_CHAR can only be used within procedures; otherwise it will fail with an invalid value returned.

Suppose the result of using CAST with large numbers or variables converted from large datatypes such as BLOB to character datatypes such as RAW is in an incompatible state. In that case, it will return NULL, and you won’t be able to access it. This is often an issue when using large numbers with this function or switching variables between character datatypes such as RAW.

To address this issue, the TO_CHAR function can be used within a procedure or function that takes an integer value as its argument and converts it to text; this method provides much safer results than using CONVERT for changing expressions that evaluate to date values.

Additionally, TO_CHAR is an extremely useful function in databases as it can convert dates to decimal characters or group separators, international currency models or group separators – providing users with multiple ways to display dates.

The CLOB function

Oracle’s TO_CHAR function converts date or interval values into strings based on user specifications. This function can be used to add, subtract or alter contents of date/time values; and it allows arithmetic operations (including comparison) on these values.

The TO_CHAR() function accepts date or number literals as arguments, where each can be either string, array, nls string or decimal digits and periods as needed. Furthermore, it can be used to typecast dates and numbers using a format model which determines their typecasting order.

TO_CHAR can convert both dates and numbers to strings using its standard format; similarly, datetime values can also be converted into strings using this approach. When doing so, each converted value includes a three digit fractional seconds portion rounded off to increments of.000,.003, or.007 seconds as part of its string equivalent; it also copies over its hours and minutes values into this string output.

To use the TO_CHAR function successfully, dates or times must be in a compatible state – this means converting datetimes to DATE datatype or using complex expressions that combine date/time into one SELECT statement as needed.

There are other data types which allow for specific formats of date and time values in strings, including the TEXT data type that supports text search features as well as BINTEXT which allows column tables but not rows tables to be created.

SQL Server also features a timestamp data type that does not stand alone as an SQL type but comes complete with its own set of functions, such as the TIMESTAMPADD and TIMESTAMPDIFF functions which calculate the difference between two dates or timestamps.

SQL Server Convert Command

Here are some examples of how you can use the SQL Server Convert Command the achieve the equivalent result.

An example SQL Server Date Conversion SQL Code

This SQL of examples runs as is, no from table required.

Select
CONVERT(VARCHAR(10), GETDATE(), 20) as
‘YYYY-MM-DD’
,CONVERT(VARCHAR(19), GETDATE(), 20) as ‘YYYY-MM-DD HH24:MI:SS’
,CONVERT(VARCHAR(8), GETDATE(), 112) as YYYYMMDD
,CONVERT(VARCHAR(6), GETDATE(), 112) as YYYYMM
,CONVERT(VARCHAR(12), DATEPART(YEAR, GETDATE()))+ RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2)
as
YYYYMM_Method_2
,CONVERT(VARCHAR(4), GETDATE(), 12) as YYMM
,CONVERT(VARCHAR(4), GETDATE(), 112) as YYYY
,CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) as YYYY_Method_2
,CONVERT(VARCHAR(4), YEAR(GETDATE())) as YYYY_Method_3
,RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) as Two_Digit_Month
,SUBSTRING(ltrim(CONVERT(VARCHAR(4), GETDATE(), 12)),3,2) as Two_Digit_Month_2
,CONVERT(VARCHAR(10), GETDATE(), 111) as ‘YYYY/MM/DD’
,CONVERT(VARCHAR(5), GETDATE(), 8) as ‘HH24:MI’

Map TO_CHAR formats to SQL Server

You can map an Oracle TO_CHAR formats to SQL Server alternative commands as follows:

Translating the formats commands

Here are some example of translating the formats commands.

Related Reference

Microsoft Docs, SQL, T-SQL Functions, GETDATE (Transact-SQL)

• https://docs.microsoft.com/en-us/sql/t-sql/functions/getdate-transact-sql

Microsoft Docs, SQL, T-SQL Functions, Date and Time Data Types and Functions (Transact-SQL)

• https://docs.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql

Microsoft Docs, SQL, T-SQL Functions, DATEPART (Transact-SQL)

• https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql