MSSQL中如何转换日期格式：YYYY-MM-DD转MM-DD-YYYY及getdate()格式化

Got you covered on both date conversion scenarios—let's break this down clearly:

1. Convert YYYY-MM-DD HH:MI:SS to MM-DD-YYYY HH:MI:SS #

First, a quick note: If your source is a datetime/datetime2 column (not a string), the actual value isn't stored in a "format"—we're just changing how it's displayed as a string. If your source is a string in YYYY-MM-DD format, we'll first cast it to a datetime type before formatting.

Option 1: Using CONVERT() (works in all SQL Server versions) #

CONVERT() uses style codes to handle date formatting. Since style 101 gives us MM/DD/YYYY, we'll swap the slashes for dashes and append the time component:

-- For a datetime column (e.g., named [order_date])
SELECT 
  REPLACE(CONVERT(VARCHAR(20), order_date, 101), '/', '-') + ' ' + 
  CONVERT(VARCHAR(8), order_date, 108) AS formatted_date;

-- If your source is a string (e.g., '2024-05-20 14:30:00')
SELECT 
  REPLACE(CONVERT(VARCHAR(20), CAST('2024-05-20 14:30:00' AS datetime2), 101), '/', '-') + ' ' + 
  CONVERT(VARCHAR(8), CAST('2024-05-20 14:30:00' AS datetime2), 108) AS formatted_date;

Option 2: Using FORMAT() (SQL Server 2012+) #

This is more readable because you can use a custom format string directly:

-- For a datetime column
SELECT FORMAT(order_date, 'MM-dd-yyyy HH:mm:ss') AS formatted_date;

-- For a string source
SELECT FORMAT(CAST('2024-05-20 14:30:00' AS datetime2), 'MM-dd-yyyy HH:mm:ss') AS formatted_date;

2. Convert GETDATE() output to MM-DD-YYYY HH:MI:SS #

GETDATE() returns a datetime value, so we can reuse the same functions above:

Using CONVERT() #

SELECT 
  REPLACE(CONVERT(VARCHAR(20), GETDATE(), 101), '/', '-') + ' ' + 
  CONVERT(VARCHAR(8), GETDATE(), 108) AS current_formatted_date;

Using FORMAT() #

SELECT FORMAT(GETDATE(), 'MM-dd-yyyy HH:mm:ss') AS current_formatted_date;

Quick Tips: #

• Performance: CONVERT() is faster than FORMAT() for large datasets, so stick with it if you're querying millions of rows.
• Datetime vs String: Remember—datetime types don't have a format. These methods convert the value to a string for display purposes. If you need to store the formatted value, use a VARCHAR column.

内容的提问来源于stack exchange，提问作者Abhilash Shajan