Format Current Date as DD/MM/YYYY in SQL Server

by

If you’re in a country that uses DD/MM/YYYY format for your dates, then you’ll likely find yourself needing to display the current date in that format when you generate reports or data that’s intended to be read by humans.

Here are four methods to format the current date as DD/MM/YYYY in SQL Server.

1. Using FORMAT() with a Custom Format String

This is probably the most straightforward approach, and easiest to understand from a readability perspective. Using a custom format string with the FORMAT() function gives you precise control over the output. This ensures that the current date is formatted exactly as you specify, regardless of your server’s regional settings.

Example:

SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');

Result:

04/08/2025

Explanation:

  • dd: Represents the day with a leading zero (e.g., 01, 31).
  • MM: Represents the month with a leading zero (e.g., 01, 12).
  • yyyy: Represents the four-digit year.

This format string guarantees a consistent DD/MM/YYYY output.

I should probably point out that the FORMAT() function was introduced in SQL Server 2012. So if using an earlier version, then this option is not for you. Try the next option instead.

2. Using CONVERT() with a Style Code

The CONVERT() function is another reliable way to achieve this format. It uses a specific style code, 103, which is explicitly defined for DD/MM/YYYY output.

Example:

SELECT CONVERT(varchar, GETDATE(), 103);

Output:

04/08/2025

Here’s what the code does:

  • CONVERT(varchar, ...): Casts the date to a string.
  • 103: This style code is the dedicated style for British/French standard date format, which is DD/MM/YYYY. This will always produce a two-digit day and month (with a leading zero if required).

3. Using FORMAT() with a Short Date Specifier and british Language

This method uses a short date specifier ('d') to specify a short date pattern (which is one of the standard date/time format strings). It then uses the session’s culture settings to determine the actual format of the short date. The british language setting uses DD/MM/YYYY as its short date standard, and so we set our session’s language to that first.

Example:

-- First, ensure the session's language is set to 'british'
SET LANGUAGE british;

SELECT FORMAT(GETDATE(), 'd');

Output:

Changed language setting to British.
------------------------------------
04/08/2025

How it works:

  • SET LANGUAGE british; This sets the session language to British English in SQL Server. This also implicitly sets the DATEFORMAT (to dmy) and DATEFIRST (to 1, for Monday), aligning with British conventions for date and time formatting (unless these settings are explicitly overridden later in the session).
  • FORMAT(GETDATE(), 'd'): The 'd' specifier formats the date according to the short date pattern defined by the session’s culture. As mentioned, this culture is primarily determined by the SET LANGUAGE command, but it can be explicitly overridden for date interpretation purposes by the SET DATEFORMAT command. For example, if the language is set to us_english (default short date format mdy), but the DATEFORMAT is explicitly changed to dmy, then the d specifier will output DD/MM/YYYY.

This option is perfect if you need to respect session-specific settings. In such cases, you would typically omit the first line.

You might also find this method useful if you need to perform a lot of formatting operations without an explicit custom format string like the one in the earlier example. In such a scenario, you could change the session’s language once, run all your FORMAT() operations, then change the language back to the original (if applicable). That said, it’s probably better to avoid changing the session’s language settings unless it’s really needed. So only use this option if none of the others are suitable.

4. String Manipulation (Concatenation)

This is more verbose than the previous examples. A lot more verbose! It involves manually extracting the day, month, and year components and then assembling them into the desired string format.

Example:

SELECT
    RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR(2)), 2) + '/' +
    RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '/' +
    CAST(YEAR(GETDATE()) AS VARCHAR(4));

Result:

04/08/2025

Run down:

  • DAY(), MONTH(), YEAR(): These functions extract the individual date parts as integers.
  • RIGHT('0' + ..., 2): This technique ensures that single-digit values (e.g., day 3, month 8) are prefixed with a leading zero (e.g., ’03’, ’08’).
  • The + signs concatenate the parts and the forward slashes into the final string.
  • CAST(... AS VARCHAR(...)): Converts the numeric parts to strings.

Not sure why anyone would choose this method over the others, but maybe it could come in handy in some situations, and that’s why I’ve listed it here.

Summary

Here are all of the above options in a nutshell:

MethodCompatibilityGuaranteed Output?Primary Advantage
FORMAT(..., 'dd/MM/yyyy')SQL Server 2012+YesModern, explicit control, highly readable
CONVERT(..., 103)All versionsYesStandard, efficient, reliable style code
FORMAT(..., 'd') + SET LANGUAGESQL Server 2012+Yes (if language set)Concise for culture-specific formats
String ManipulationAll versionsYesUniversal (works on all versions of SQL Server)