• Revolutionizing Efficiency: The Power of Custom Automation Software Development
• SQL Server and AI: Integrating Machine Learning Services
• From the SQL Server Central Blogs - Measuring and Improving SQL Server Query Plan Cache Efficiency
• From the SQL Server Central Blogs - Communicate to the Individual
• Architecting Power BI Solutions in Microsoft Fabric

• Query Plan Regressions --

Formatting Dates and Times: The SQL Dialect Divide

If you’ve ever had to format dates and times in SQL, you’ve probably come across one of the most jarring realities of working across platforms: every major RDBMS does it differently and sometimes confusingly.  What’s a TO_CHAR() in Oracle becomes FORMAT() or CONVERT() in SQL Server, and its sort of the same in PostgreSQL… but not exactly.

Let’s break down the two key players in date and time formatting and conversion, starting with Oracle, which are:

·      TO_CHAR

·      TO_DATE

Depending on the reason for the format, joins, etc., one of these are often used and how they compare across Oracle, SQL Server, and PostgreSQL will be different.

Oracle: The Source of TO_CHAR and TO_DATE

Oracle is where TO_CHAR and TO_DATE originated and where they’re used most heavily.  I remember being very frustrated while learning how to format and how often I'd discovery code that would convert, then convert again and convert back a third time as part of processes, wondering why no one checked the original format of dates, timestamps, etc.

• TO_CHAR(date, format) is used to format a date or timestamp to a string:

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;

• TO_DATE(string, format) is used to convert a string into a DATE datatype:

SELECT TO_DATE('2025-07-07', 'YYYY-MM-DD') FROM dual;

Oracle does give rich control with hundreds of formatting tokens.  It supports multiple timestamp types (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE), and a default option, which is often set by the code called in the PL/SQL.

SQL Server: CONVERT, FORMAT, and Implicit Magic

SQL Server doesn’t use TO_CHAR or TO_DATE, as these are functions built into Oracle. Instead, it gives you two formatting functions and plenty of implicit casting.  Some of this is considered helpful, some may consider it frustrating or even dangerous.

Formatting a date to string is efficient and simple, using CONVERT() and FORMAT():

• CONVERT() with style codes (fast but cryptic):

SELECT CONVERT(VARCHAR, GETDATE(), 120);  -- 'YYYY-MM-DD HH:MI:SS'

• FORMAT() (slow but readable and flexible):

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');

We are able to do converting (in most cases) of a string to a date (like TO_DATE):

• SQL Server does it implicitly in many cases:

SELECT CAST('2025-07-07' AS DATETIME);

• Or explicitly with CONVERT():

SELECT CONVERT(DATETIME, '2025-07-07', 120);

SQL Server is the most “magical” of the three, often guessing what you meant, which can cause regional or cultural format issues (e.g., MM/DD/YYYY vs. DD/MM/YYYY) unless you’re very specific.

PostgreSQL: Clean, Predictable, Familiar

PostgreSQL embraces the TO_CHAR and TO_DATE functions into it’s own platform, just like Oracle, but with a more concise, Linux-like sensibility.

• Format date to string:

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');

• Convert string to date:

SELECT TO_DATE('2025-07-07', 'YYYY-MM-DD');

PostgreSQL also excels in interval handling and arithmetic, allowing you to perform clean operations like NOW() + INTERVAL '3 days', something which is trickier in SQL Server.

Function Mapping Across Platforms

Interesting enough, it's not just TO_CHAR or TO_DATE, but we also have a third, in the area of convert to string, which I've added into the below table.  It's an additional option available in the three platforms:

Definitely Know Your Dialect

The way SQL handles dates is a perfect reflection of its underlying design philosophy.  Each platform has its structure and flexibility, which goes hand-in-hand with the database platform:

• Oracle is strict, detailed, and expects you to be explicit.

• SQL Server is flexible to a fault, even leaning heavily on implicit behavior unless you opt into formatting.

• PostgreSQL strikes a nice balance, which is clean, powerful, and consistent.

For database professionals working across environments, understanding these differences is essential.  It’s not just for ensuring correct formatting, but for writing portable code, debugging date issues, and avoiding painful localization bugs. Mastering date/time conversions may not feel glamorous, but it’s one of the clearest signs of a seasoned engineer.

After all, it’s not the flashy queries that trip you up, but the datetime strings that won’t behave.

DBAKevlar

Join the debate, and respond to today's editorial on the forums