In SQL Server, we can use functions like GETDATE() to get the current date and time. There are also other functions, like CURRENT_TIMESTAMP, SYSDATETIME(), etc. These functions return values using one of the valid date/time types. For example, GETDATE() and CURRENT_TIMESTAMP return a datetime type, while SYSDATETIME() returns a datetime2(7) value.
Either way, if we want the current date to be displayed using MM/DD/YYYY format, we’ll need to do some extra work.
Fortunately SQL Server provides us with a range of options for doing this, and so we can pick the one that suits our scenario.
With that in mind, here are four ways to format the current date as MM/DD/YYYY in SQL Server.
1. Using FORMAT() with an Explicit Format String
One of the easiest ways to do it is with the FORMAT() function along with a custom format string that explicitly defines the output format as MM/DD/YYYY .
This is a reliable option for getting the current date into MM/DD/YYYY format. The FORMAT() function’s sole purpose is to format a given value in our chosen format. In the case of date and time values, it accepts a format string that matches our goal perfectly: 'MM/dd/yyyy'
Here it is:
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy');Result:
08/03/2025
Here, 'MM/dd/yyyy' is an explicit format string. It directly tells the function how to format the date, regardless of the session’s language or culture. When using this format string, there will be no confusion over the actual format we require.
I should point out that capitalization matters with the format string. You’ll notice that 'MM/dd/yyyy' uses uppercase for the month, and lowercase for the day and year. This is important, as changing the case will change the outcome (for example, changing MM to mm will output minutes instead of the month). So if you find that your dates are not being output in the right format, check the casing of your format specifiers.
I should also mention that FORMAT() was introduced in SQL Server 2012, and so if you’re using an older version, you’ll need to use another option.
2. Using FORMAT() with a Short Date Specifier ('d')
In the previous example we used a custom format string with the FORMAT() function. This was to ensure that there was no question about the format that we wanted our date to be output in. In this example, we’ll use a standard date/time format string. This consists of a single date specifier that is kind of like a shortcut for the format we want.
However, it comes with a caveat.
The output will be MM/DD/YYYY only if the session’s settings use that specific date format (or if we explicitly specify the language via the FORMAT() function’s culture argument). In particular, it will work only if the session’s DATEFORMAT setting is configured to mdy, or if the LANGUAGE setting uses a language that defaults to a mdy date format (such as us_english) is used without an explicit DATEFORMAT override.
Here’s an example that demonstrates what I mean:
-- First, set the language to 'us_english' which uses MM/DD/YYYY as its short date format
SET LANGUAGE us_english;
GO
SELECT FORMAT(GETDATE(), 'd') AS ShortDate;
GO
-- Now, set the language to 'british' which uses DD/MM/YYYY as its short date format
SET LANGUAGE british;
GO
SELECT FORMAT(GETDATE(), 'd') AS ShortDate;
GOOutput:
Changed language setting to us_english.
ShortDate
----------
8/3/2025
Changed language setting to British.
ShortDate
---------
03/08/2025
Now, as alluded to, we can also specify the language directly in the FORMAT() function:
SELECT
FORMAT(GETDATE(), 'd', 'en-US') AS 'en-US',
FORMAT(GETDATE(), 'd', 'en-GB') AS 'en-GB';Result:
en-US en-GB
--------- ----------
8/3/2025 03/08/2025
This forces SQL Server to ignore the session language.
You’ll notice that the British format uses leading zeros (03/08/2025) but the US format doesn’t (8/3/2025). This is to be expected. The FORMAT() function relies on the .NET Framework’s globalization and culture settings. This has the following result:
- US English (
en-US) Culture: The short date format for the US English culture isM/d/yyyy. It does not use leading zeros for single-digit months or days. - British English (
en-gb) Culture: The short date format for the British English culture isdd/MM/yyyy. This format explicitly uses two digits with leading zeros for both the day and the month.
So while 'd' is a viable option, its output is not fixed, and there are nuances around how the date is actually formatted. For a guaranteed MM/DD/YYYY output, the explicit format string 'MM/dd/yyyy' (that we used in the previous example) is more reliable.
3. Using CONVERT() with a Style Code (101)
This widely-used method works on all versions of SQL Server. If you’re using an older version of SQL Server that doesn’t support the FORMAT() function, then the CONVERT() function is for you.
The CONVERT() function allows us to convert between data types. In our case, we’ll be converting to a string, such as varchar (due to the fact that we need to output the current date in a format that the date type doesn’t support). The function accepts an optional style argument, which is what we can use to specify how we want the current date to be formatted. For our purposes, we’ll need to use style code 101. This style code represents MM/DD/YYYY.
Example:
SELECT CONVERT(varchar, GETDATE(), 101);Result:
08/03/2025
The output doesn’t change depending on the session’s settings like in the previous example. The output will be exactly as shown regardless.
You may have noticed that this output includes leading zeros (which is contrary to the output we got for us_english when we used 'd' with the FORMAT() function). The CONVERT() function is not culture aware (and it doesn’t use the .NET Framework’s globalization and culture settings like FORMAT() does). Therefore, CONVERT()‘s style arguments result in a fixed format. In our case, style 101 results in a fixed format of MM/DD/YYYY, including leading zeros, regardless.
4. String Manipulation (Concatenation)
This is a less efficient but universal method for formatting dates. It involves extracting the month, day, and year parts and then concatenating them.
Example:
SELECT
RIGHT('0' + CAST(MONTH(GETDATE()) AS VARCHAR(2)), 2) + '/' +
RIGHT('0' + CAST(DAY(GETDATE()) AS VARCHAR(2)), 2) + '/' +
CAST(YEAR(GETDATE()) AS VARCHAR(4));Output:
08/03/2025
Yes, quite a convoluted method compared to the other methods, but at least it demonstrates another way to achieve our desired format for the current date.
Summary
Here are the four formats in a nutshell:
| Method | SQL Server Version | Guaranteed Output? | Advantages | Disadvantages |
FORMAT('MM/dd/yyyy') | 2012+ | Yes | Modern, readable, flexible | Not available on older versions |
FORMAT('d') | 2012+ | No | Concise, respects local culture | Output depends on session’s language setting |
CONVERT(..., 101) | All | Yes | Standard, efficient | Requires knowing the style code |
| String Manipulation | All | Yes | Universal (works on all versions of SQL Server) | Verbose, less readable, can be error-prone |