How to Convert DateTime to VarChar with Specific Formatting in SQL Server?

Specific format conversion from datetime to VarChar in SQL Server

It is often necessary to convert a value stored as a DateTime variable into a VarChar variable formatted in a specific format (such as "yyyy-mm-dd"). This conversion is particularly useful for displaying or storing dates in a consistent and easy-to-read format.

Using the CONVERT() function along with one of the supported style options, you can achieve your desired formatting. Here’s how:

<code class="language-sql">SELECT CONVERT(nvarchar(MAX), @date_value, <style_code>) AS formatted_date;</code>

In this syntax:

• @date_value is the DateTime value you want to convert.
• <style_code> are style codes specifying the desired output format.

The following table lists the available style codes and their corresponding formats.

样式代码	输出格式
0	"dd mmm yyyy hh:mm:ss tt"
1	"dd/mm/yy"
2	"yy.mm.dd"
3	"dd/mm/yyyy"
4	"dd.mm.yyyy"
5	"dd-mm-yyyy"
6	"dd Mon yy"
7	"Mon dd, yy"
8	"hh:mm:ss tt"
9	"dd mmm yyyy hh:mm:ss tt (nnnn)"
10	"dd-mm-yyyy hh:mm:ss"
11	"yyyy-mm-dd hh:mm:ss"
12	"yyyymmdd"
13	"dd Mon yyyy hh:mm:ss tt"
14	"hh:mm:ss"
20	"yyyy-mm-dd hh:mm:ss.fff"
21	"yyyy-mm-dd hh:mm:ss.fffffff"
22	"dd/mm/yyyy hh:mm:ss AM/PM"
23	"yyyy-mm-dd"
24	"hh:mm"
25	"yyyy-mm-dd hh:mm:ss.fffffff"
100	"dd mmm yyyy hh:mm:ss tt (nnnn) TZ"
101	"dd/mm/yyyy hh:mm:ss (z)"
102	"yy.mm.dd hh:mm:ss (z)"
103	"dd/mm/yyyy hh:mm:ss"
104	"dd.mm.yyyy hh:mm:ss"
105	"dd-mm-yyyy hh:mm:ss"
106	"dd Mon yy hh:mm:ss (z)"
107	"Mon dd, yy hh:mm:ss (z)"
108	"hh:mm:ss (z)"
109	"dd mmm yyyy hh:mm:ss tt (nnnn) TZ"
110	"dd-mm-yyyy hh:mm:ss (z)"
111	"yyyy-mm-dd hh:mm:ss (z)"
112	"yyyymmdd hh:mm:ss"
113	"dd Mon yyyy hh:mm:ss tt (z)"
114	"hh:mm:ss (z)"
120	"yyyy-mm-dd hh:mm:ss.fff (z)"
121	"yyyy-mm-dd hh:mm:ss.fffffff (z)"
126	"yyyy-mm-ddTHH:mm:ss.fffffffZ"
127	"yyyy-mm-ddTHH:mm:ss.fffffff"
130	"dd جمادى الثانية mm hh:mm:ss.fffffff"
131	"dd/mm/yyyy hh:mm:ss AM/PM (z)"

To convert a DateTime value to a VarChar value in the format "yyyy-mm-dd", you can use the following method:

<code class="language-sql">SELECT CONVERT(nvarchar(MAX), @date_value, <style_code>) AS formatted_date;</code>

Note that the output VarChar value will have the format specified, but it will still represent a point in time with date and time components. If you just want to get the date part without the time, you can use the following technique:

<code class="language-sql">SELECT CONVERT(nvarchar(MAX), @date_value, 23) AS formatted_date;</code>

This will truncate the time component and return only the VarChar value with "yyyy-mm-dd" format.

The above is the detailed content of How to Convert DateTime to VarChar with Specific Formatting in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!