Skip to main content

T-SQL Format DATE or DATETIME as String. 44 Common Examples of Using FORMAT() Function.

In this post, we will demonstrate 44 common examples of formatting DATE or DATETIME values to string using the built-in FORMAT function available in SQL Server 2012 and later versions.

You can use this Fiddle to follow along and practice formatting DATETIME to String

T-SQL Format DATE or DATETIME as String. 44 Common Examples of Using FORMAT() Function.
SELECT GETDATE() AS CurrentDateTime, 'yyyy-MM-dd hh:mm:ss tt' AS FormatOption, FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss tt') AS StyleOutput
UNION ALL
SELECT GETDATE() AS CurrentDateTime, 'yyyyMMddHHmmss', FORMAT(GETDATE(), 'yyyyMMddHHmmss')
UNION ALL
SELECT GETDATE(), 'MM/dd/yy', FORMAT(GETDATE(), 'MM/dd/yy')
UNION ALL
SELECT GETDATE(), 'yy.MM.dd', FORMAT(GETDATE(), 'yy.MM.dd')
UNION ALL
SELECT GETDATE(), 'dd/MM/yy', FORMAT(GETDATE(), 'dd/MM/yy')
UNION ALL
SELECT GETDATE(), 'dd.MM.yy', FORMAT(GETDATE(), 'dd.MM.yy')
UNION ALL
SELECT GETDATE(), 'dd-MM-yy', FORMAT(GETDATE(), 'dd-MM-yy')
UNION ALL
SELECT GETDATE(), 'dd MMM yy', FORMAT(GETDATE(), 'dd MMM yy')
UNION ALL
SELECT GETDATE(), 'MMM dd, yy', FORMAT(GETDATE(), 'MMM dd, yy')
UNION ALL
SELECT GETDATE(), 'HH:mm:ss', FORMAT(GETDATE(), 'HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'MMM dd yyyy HH:mm:ss tt', FORMAT(GETDATE(), 'MMM dd yyyy HH:mm:ss tt')
UNION ALL
SELECT GETDATE(), 'MM-dd-yy', FORMAT(GETDATE(), 'MM-dd-yy')
UNION ALL
SELECT GETDATE(), 'yy/MM/dd', FORMAT(GETDATE(), 'yy/MM/dd')
UNION ALL
SELECT GETDATE(), 'yyyyMMdd', FORMAT(GETDATE(), 'yyyyMMdd')
UNION ALL
SELECT GETDATE(), 'dd MMM yyyy HH:mm:ss', FORMAT(GETDATE(), 'dd MMM yyyy HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'HH:mm:ss.fff', FORMAT(GETDATE(), 'HH:mm:ss.fff')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-dd HH:mm:ss', FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff', FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff')
UNION ALL
SELECT GETDATE(), 'MM/dd/yyyy hh:mm:ss tt', FORMAT(GETDATE(), 'MM/dd/yyyy hh:mm:ss tt')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-dd', FORMAT(GETDATE(), 'yyyy-MM-dd')
UNION ALL
SELECT GETDATE(), 'dd/MM/yyyy', FORMAT(GETDATE(), 'dd/MM/yyyy')
UNION ALL
SELECT GETDATE(), 'dd.MM.yyyy', FORMAT(GETDATE(), 'dd.MM.yyyy')
UNION ALL
SELECT GETDATE(), 'yyyy.MM.dd', FORMAT(GETDATE(), 'yyyy.MM.dd')
UNION ALL
SELECT GETDATE(), 'dd/MM/yyyy HH:mm:ss', FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'MMM dd yyyy HH:mm:ss tt', FORMAT(GETDATE(), 'MMM dd yyyy HH:mm:ss tt')
UNION ALL
SELECT GETDATE(), 'MM/dd/yyyy', FORMAT(GETDATE(), 'MM/dd/yyyy')
UNION ALL
SELECT GETDATE(), 'yyyy.MM.dd', FORMAT(GETDATE(), 'yyyy.MM.dd')
UNION ALL
SELECT GETDATE(), 'dd/MM/yyyy', FORMAT(GETDATE(), 'dd/MM/yyyy')
UNION ALL
SELECT GETDATE(), 'dd.MM.yyyy', FORMAT(GETDATE(), 'dd.MM.yyyy')
UNION ALL
SELECT GETDATE(), 'dd-MM-yyyy', FORMAT(GETDATE(), 'dd-MM-yyyy')
UNION ALL
SELECT GETDATE(), 'dd MMM yyyy', FORMAT(GETDATE(), 'dd MMM yyyy')
UNION ALL
SELECT GETDATE(), 'MMM dd, yyyy', FORMAT(GETDATE(), 'MMM dd, yyyy')
UNION ALL
SELECT GETDATE(), 'HH:mm:ss', FORMAT(GETDATE(), 'HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'MMM dd yyyy HH:mm:ss tt', FORMAT(GETDATE(), 'MMM dd yyyy HH:mm:ss tt')
UNION ALL
SELECT GETDATE(), 'MM-dd-yyyy', FORMAT(GETDATE(), 'MM-dd-yyyy')
UNION ALL
SELECT GETDATE(), 'yyyy/MM/dd', FORMAT(GETDATE(), 'yyyy/MM/dd')
UNION ALL
SELECT GETDATE(), 'yyyyMMdd', FORMAT(GETDATE(), 'yyyyMMdd')
UNION ALL
SELECT GETDATE(), 'dd MMM yyyy HH:mm:ss', FORMAT(GETDATE(), 'dd MMM yyyy HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'HH:mm:ss.fff', FORMAT(GETDATE(), 'HH:mm:ss.fff')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-dd HH:mm:ss', FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff', FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss.fff')
UNION ALL
SELECT GETDATE(), 'yyyy-MM-ddTHH:mm:ss.fff', FORMAT(GETDATE(), 'yyyy-MM-ddTHH:mm:ss.fff')
UNION ALL
SELECT GETDATE(), 'dd MMM yyyy HH:mm:ss.fff tt', FORMAT(GETDATE(), 'dd MMM yyyy HH:mm:ss.fff tt')
UNION ALL
SELECT GETDATE(), 'dd/MM/yyyy HH:mm:ss.fff tt', FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss.fff tt');
CurrentDateTimeFormatOptionStyleOutput
2023-08-18 18:17:42.420yyyy-MM-dd hh:mm:ss tt2023-08-18 06:17:42 PM
2023-08-18 18:17:42.420yyyyMMddHHmmss20230818181742
2023-08-18 18:17:42.420MM/dd/yy08/18/23
2023-08-18 18:17:42.420yy.MM.dd23.08.18
2023-08-18 18:17:42.420dd/MM/yy18/08/23
2023-08-18 18:17:42.420dd.MM.yy18.08.23
2023-08-18 18:17:42.420dd-MM-yy18-08-23
2023-08-18 18:17:42.420dd MMM yy18 Aug 23
2023-08-18 18:17:42.420MMM dd, yyAug 18, 23
2023-08-18 18:17:42.420HH:mm:ss18:17:42
2023-08-18 18:17:42.420MMM dd yyyy HH:mm:ss ttAug 18 2023 18:17:42 PM
2023-08-18 18:17:42.420MM-dd-yy08-18-23
2023-08-18 18:17:42.420yy/MM/dd23/08/18
2023-08-18 18:17:42.420yyyyMMdd20230818
2023-08-18 18:17:42.420dd MMM yyyy HH:mm:ss18 Aug 2023 18:17:42
2023-08-18 18:17:42.420HH:mm:ss.fff18:17:42.420
2023-08-18 18:17:42.420yyyy-MM-dd HH:mm:ss2023-08-18 18:17:42
2023-08-18 18:17:42.420yyyy-MM-dd HH:mm:ss.fff2023-08-18 18:17:42.420
2023-08-18 18:17:42.420MM/dd/yyyy hh:mm:ss tt08/18/2023 06:17:42 PM
2023-08-18 18:17:42.420yyyy-MM-dd2023-08-18
2023-08-18 18:17:42.420dd/MM/yyyy18/08/2023
2023-08-18 18:17:42.420dd.MM.yyyy18.08.2023
2023-08-18 18:17:42.420yyyy.MM.dd2023.08.18
2023-08-18 18:17:42.420dd/MM/yyyy HH:mm:ss18/08/2023 18:17:42
2023-08-18 18:17:42.420MMM dd yyyy HH:mm:ss ttAug 18 2023 18:17:42 PM
2023-08-18 18:17:42.420MM/dd/yyyy08/18/2023
2023-08-18 18:17:42.420yyyy.MM.dd2023.08.18
2023-08-18 18:17:42.420dd/MM/yyyy18/08/2023
2023-08-18 18:17:42.420dd.MM.yyyy18.08.2023
2023-08-18 18:17:42.423dd-MM-yyyy18-08-2023
2023-08-18 18:17:42.423dd MMM yyyy18 Aug 2023
2023-08-18 18:17:42.423MMM dd, yyyyAug 18, 2023
2023-08-18 18:17:42.423HH:mm:ss18:17:42
2023-08-18 18:17:42.423MMM dd yyyy HH:mm:ss ttAug 18 2023 18:17:42 PM
2023-08-18 18:17:42.423MM-dd-yyyy08-18-2023
2023-08-18 18:17:42.423yyyy/MM/dd2023/08/18
2023-08-18 18:17:42.423yyyyMMdd20230818
2023-08-18 18:17:42.423dd MMM yyyy HH:mm:ss18 Aug 2023 18:17:42
2023-08-18 18:17:42.423HH:mm:ss.fff18:17:42.423
2023-08-18 18:17:42.423yyyy-MM-dd HH:mm:ss2023-08-18 18:17:42
2023-08-18 18:17:42.423yyyy-MM-dd HH:mm:ss.fff2023-08-18 18:17:42.423
2023-08-18 18:17:42.423yyyy-MM-ddTHH:mm:ss.fff2023-08-18T18:17:42.423
2023-08-18 18:17:42.423dd MMM yyyy HH:mm:ss.fff tt18 Aug 2023 18:17:42.423 PM
2023-08-18 18:17:42.423dd/MM/yyyy HH:mm:ss.fff tt18/08/2023 18:17:42.423 PM

The T-SQL FORMAT function is used to format a datetime value into a specific string representation based on a format pattern. Here are the benefits of using the FORMAT function:

  1. Customized Formatting: The FORMAT function allows you to define custom formatting patterns to display datetime values exactly the way you want. You can specify patterns for years, months, days, hours, minutes, seconds, and more.

  2. Consistency: Using FORMAT ensures consistent date and time formatting across your queries or reports. This is especially useful in scenarios where you need to present data in different formats to users or applications.

  3. Simplicity: With FORMAT, you can achieve complex datetime formatting with a single function call. This can simplify your SQL code and make it easier to read and maintain.

  4. Localization: The FORMAT function respects the culture settings of your database, allowing you to format dates and times according to regional preferences. This is helpful when dealing with internationalization and localization of your application.

  5. Avoiding Conversion Issues: When you need to display datetime values as strings, using FORMAT avoids the need to explicitly convert datetime values to strings using CONVERT or CAST. This can prevent unexpected formatting issues that might arise from conversion.

However, it's important to consider some caveats:

  1. Performance: While FORMAT is convenient, it can be slower compared to other date manipulation functions like CONVERT due to its more complex processing. It might not be suitable for high-performance scenarios, such as large-scale data operations.

  2. Compatibility: The FORMAT function is available in SQL Server 2012 and later versions. If your application needs to be compatible with earlier versions, you might need to use other date manipulation techniques.

  3. Resource Usage: Using FORMAT can consume more resources compared to simple date extraction methods, especially if used in queries with a large number of records.

In summary, the FORMAT function in T-SQL provides a powerful tool for customizing datetime formatting, enhancing consistency, and improving code readability. However, you should weigh its benefits against performance considerations and compatibility requirements for your specific use case.