You know what you want the date to look like. You just need the SQL to produce it. So instead of starting with functions and working forward, this article starts with formats and works backward. Each heading is a format you might want, with the code to get you there.
All examples use GETDATE() for simplicity. Replace it with your column name and the query works the same way.
I Want Just the Date, No Time
Here are the more common formats for date-only values.
YYYY-MM-DD (e.g. 2026-06-24)
The ISO standard format. Good for sorting, APIs, and anywhere you need an unambiguous date.
SELECT
CONVERT(VARCHAR, GETDATE(), 23) AS UsingConvert,
FORMAT(GETDATE(), 'yyyy-MM-dd') AS UsingFormat;DD/MM/YYYY (e.g. 24/06/2026)
The standard format for the UK, Australia, and most of Europe.
SELECT
CONVERT(VARCHAR, GETDATE(), 103) AS UsingConvert,
FORMAT(GETDATE(), 'dd/MM/yyyy') AS UsingFormat;MM/DD/YYYY (e.g. 06/24/2026)
The US date format.
SELECT
CONVERT(VARCHAR, GETDATE(), 101) AS UsingConvert,
FORMAT(GETDATE(), 'MM/dd/yyyy') AS UsingFormat;DD-MM-YYYY (e.g. 24-06-2026)
SELECT
CONVERT(VARCHAR, GETDATE(), 105) AS UsingConvert,
FORMAT(GETDATE(), 'dd-MM-yyyy') AS UsingFormat;DD.MM.YYYY (e.g. 24.06.2026)
Common in Germany and other parts of central Europe.
SELECT
CONVERT(VARCHAR, GETDATE(), 104) AS UsingConvert,
FORMAT(GETDATE(), 'dd.MM.yyyy') AS UsingFormat;YYYYMMDD (e.g. 20260624)
No separators. Useful for filenames, report codes, or anywhere you need a compact sortable date string.
SELECT
CONVERT(VARCHAR, GETDATE(), 112) AS UsingConvert,
FORMAT(GETDATE(), 'yyyyMMdd') AS UsingFormat;DD Mon YYYY (e.g. 24 Jun 2026)
A readable format that avoids ambiguity between day and month.
SELECT
CONVERT(VARCHAR, GETDATE(), 106) AS UsingConvert,
FORMAT(GETDATE(), 'dd MMM yyyy') AS UsingFormat;Month DD, YYYY (e.g. June 24, 2026)
CONVERT() can’t produce a full month name. You need FORMAT() for this one.
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy') AS FullMonthName;Day, DD Month YYYY (e.g. Wednesday, 24 June 2026)
Again, only FORMAT() can produce day and month names.
SELECT FORMAT(GETDATE(), 'dddd, dd MMMM yyyy') AS FullDayAndMonth;I Want the Date and Time Together
This section contains the more common formats for datetime values.
YYYY-MM-DD HH:MM:SS (e.g. 2026-06-24 09:15:30)
The most common datetime format for logs, audit trails, and data exports.
SELECT
CONVERT(VARCHAR, GETDATE(), 120) AS UsingConvert,
FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss') AS UsingFormat;YYYY-MM-DD HH:MM:SS.mmm (e.g. 2026-06-24 09:15:30.123)
Includes milliseconds. Useful when you need sub-second precision in your output.
SELECT
CONVERT(VARCHAR, GETDATE(), 121) AS UsingConvert,
FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff') AS UsingFormat;ISO 8601 (e.g. 2026-06-24T09:15:30.000)
The format most APIs and external systems expect. The T separator between date and time is the distinguishing feature.
SELECT
CONVERT(VARCHAR, GETDATE(), 127) AS UsingConvert,
FORMAT(GETDATE(), 'yyyy-MM-ddTHH:mm:ss.fff') AS UsingFormat;12-Hour Clock With AM/PM (e.g. 06/24/2026 9:15:30 AM)
CONVERT() has no 12-hour clock format. Use FORMAT().
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy h:mm:ss tt') AS TwelveHourClock;I Want Just the Time
You can also output just the time portion of the datetime value (and format it as required).
HH:MM:SS (e.g. 09:15:30)
SELECT
CONVERT(VARCHAR, GETDATE(), 108) AS UsingConvert,
FORMAT(GETDATE(), 'HH:mm:ss') AS UsingFormat;HH:MM (e.g. 09:15)
CONVERT() can’t drop the seconds. Use FORMAT().
SELECT FORMAT(GETDATE(), 'HH:mm') AS HoursAndMinutes;9:15 AM (e.g. 12-hour with AM/PM, no seconds)
SELECT FORMAT(GETDATE(), 'h:mm tt') AS TwelveHourTime;I Want a Specific Part of the Date
If you just need a single component extracted as a number or name, DATEPART() and DATENAME() are cleaner than formatting the whole date and slicing it.
SELECT
YEAR(GETDATE()) AS YearNumber,
MONTH(GETDATE()) AS MonthNumber,
DAY(GETDATE()) AS DayNumber,
DATEPART(quarter, GETDATE()) AS QuarterNumber,
DATEPART(week, GETDATE()) AS WeekNumber,
DATENAME(month, GETDATE()) AS MonthName,
DATENAME(weekday, GETDATE()) AS WeekdayName;I Want to Format a Number or Currency
CONVERT() can’t format numbers beyond basic type conversion. All of the following use FORMAT().
Thousands Separators (e.g. 1,234,567.89)
SELECT
FORMAT(1234567.89, 'N2') AS TwoDecimalPlaces,
FORMAT(1234567.89, 'N0') AS NoDecimalPlaces;Currency With Symbol
SELECT
FORMAT(1234567.89, 'C2', 'en-US') AS USDollars,
FORMAT(1234567.89, 'C2', 'en-GB') AS BritishPounds,
FORMAT(1234567.89, 'C2', 'de-DE') AS Euros;Percentage (e.g. 15.30%)
SELECT FORMAT(0.153, 'P2') AS Percentage;Zero-Padded Number (e.g. 00001234)
Useful for reference numbers, invoice IDs, or any numeric value that needs a fixed width.
SELECT FORMAT(1234, '00000000') AS PaddedNumber;A Note on Performance
For most of the date formats above, both CONVERT() and FORMAT() produce the same result. Where both options exist, CONVERT() is the faster choice on large datasets. FORMAT() runs through the .NET runtime rather than SQL Server’s native engine, which adds overhead that compounds at scale.
For small queries, reports, or one-off lookups, the difference is usually negligible. For anything scanning millions of rows, stick with CONVERT() where you can.