Converting Datetime to String using CONVERT() Function in SQL Server
As a developer, you may find yourself wanting to represent datetime values in a more readable format for easier data analysis and processing. Fortunately, SQL Server offers a powerful function called CONVERT() that can convert datetime values to strings in various formats.
Syntax of CONVERT() Function
The syntax of the CONVERT() function is as follows:
CONVERT(data_type, expression, style)
where data_type represents the target data type, expression represents the value to be converted, and style represents the desired format of the output string.
Valid Styles and Corresponding Formats of Datetime
Understanding the different styles available in CONVERT() is crucial for datetime-to-string conversions. A style is an integer value representing the format of the output string.
The following table shows the valid styles and their corresponding formats:
| Style Number | Format |
|---|---|
| 0 | yyyy-MM-dd hh:mm:ss |
| 1 | yyyy/MM/dd hh:mm:ss |
| 2 | yy/MM/dd hh:mm:ss |
| 3 | dd/MM/yy hh:mm:ss |
| 4 | dd.MM.yy hh:mm:ss |
| 5 | dd-MM-yyThh:mm:ss |
| 6 | dd MMM yyyy hh:mm:ss:fff |
| 7 | MM/dd/yy hh:mm:ss |
| 8 | hh:mm:ss |
| 9 | yyyy-MM-ddThh:mm:ss |
| 10 | yyyy-mm-dd |
| 11 | hh:mm:ss |
| 12 | yymmdd |
| 13 | dd MMM yyyy hh:mm:ss |
| 14 | yyyy-MM-dd hh:mm:ss |
| 20 | yyyy-MM-dd hh:mm:ss |
| 21 | yyyy-MM-ddThh:mm:ss:fff |
| 22 | mm/dd/yy hh:mm:ss AM/PM |
| 23 | yyyy-MM-ddThh:mm:ss |
| 24 | hh:mm:ss |
| 25 | yyyy-mm-ddThh:mm:ss |
| 100 | mm/dd/yyyy |
| 101 | yyyy/MM/dd |
| 102 | yyyy.MM.dd |
| 103 | dd/MM/yyyy |
| 104 | dd.MM.yyyy |
| 105 | dd-MM-yyyy |
| 106 | dd MMM yyyy |
| 107 | MMM dd, yyyy |
| 108 | hh:mm:ss |
| 109 | MMM dd yyyy hh:mm:ss:fff |
| 110 | mm-dd-yyyy |
Examples of Datetime to String Conversion
The following examples illustrate how to use CONVERT() to convert datetime values to strings:
— Example 1
SELECT CONVERT(varchar(20), GETDATE(), 101);Output: ’09/22/2021′
In this example, we use the GETDATE() function to retrieve the current date and time and then apply the CONVERT() function with a style of 101 to convert it into a string representation in the mm/dd/yyyy format. — Example 2
— Example 2
SELECT CONVERT(varchar(20), '2021-09-22 14:36:23.570', 107);Output: ‘Sep 22, 2021’
In this example, we use the CONVERT() function to convert the given datetime string into a string representation in the MMM dd, yyyy format.
— Example 3
SELECT CONVERT(varchar(20), '2021-09-22T14:36:23.570', 25);Output: ‘2021-09-22 14:36:23’
In this example, we use the CONVERT() function to convert the given datetime string into a string representation in the yyyy-mm-ddThh:mm:ss format.
Conclusion
In conclusion, the CONVERT() function is a powerful tool for converting datetime values to string representations in various formats. Understanding the different styles available in CONVERT() is crucial for datetime-to-string conversions.
By following the syntax and valid styles, you can easily convert datetime values to strings for easier data analysis and processing. Use the above examples as a template to practice datetime-to-string conversions in SQL Server.
In summary, converting datetime values to string representations using the CONVERT() function in SQL Server is an essential skill for developers looking to simplify data analysis and processing. To convert datetime values to strings, it is important to understand the syntax of the CONVERT() function and the valid styles and corresponding formats available.
By following the examples provided in this article, you can improve your understanding and practice datetime-to-string conversions in SQL Server. Remember that mastering this skill can increase your data analysis efficiency and accuracy.