SSIS加载CSV至SQL Server时的日期时间格式调整问题求助

Hey Jana, let's work through this date format issue in your SSIS CSV-to-SQL Server workflow. I'll break down the solutions based on whether you need to fix how the date is parsed during loading, adjust how it's displayed, or store it in a specific string format.

First, remember that SQL Server's datetime/datetime2 types don't store a display format—what you see in tools like SSMS depends on client settings. But if the date is showing up as yyyy-dd-MM, that probably means SSIS didn't parse the CSV's MM/dd/yy hh:mm:ss format correctly.

• Step 1: Configure Flat File Connection Manager

  • Open your Flat File Connection Manager, go to the Advanced tab.
  • Find your date column, set its DataType to datetime (or DT_DBTIMESTAMP in the data flow).
  • Set LocaleID to 1033 (English - United States) and turn off FastParse—this tells SSIS to properly interpret the MM/dd/yy date pattern from your CSV.

• Step 2: Adjust SSMS Display Settings

  • If you just want to see dates as dd/MM/yyyy hh:mm:ss in SSMS:
    • Run SET DATEFORMAT dmy; at the start of your query session—this forces SSMS to display dates in day/month/year order.
    • Or change SSMS defaults: Go to Tools > Options > SQL Server Object Explorer > Commands and set the Default date format to dd/MM/yyyy.

If your SQL Server column is a string type (varchar/nvarchar) and you need to store dates exactly as dd/MM/yyyy hh:mm:ss:

• Add a Derived Column transformation to your SSIS Data Flow.
• Create a new derived column with this expression (replace YourDateColumn with your actual column name):

  FORMAT([YourDateColumn], "dd/MM/yyyy HH:mm:ss")
  

  Note: The FORMAT function requires SQL Server 2012 or later. If you're using an older version, use this instead:

  RIGHT("0" + (DT_WSTR,2)DAY([YourDateColumn]),2) + "/" + 
  RIGHT("0" + (DT_WSTR,2)MONTH([YourDateColumn]),2) + "/" + 
  (DT_WSTR,4)YEAR([YourDateColumn]) + " " + 
  SUBSTRING((DT_WSTR,20)[YourDateColumn], 12, 8)
  

• Set the derived column's data type to DT_WSTR, 19 (since the formatted string is 19 characters long), then map it to your SQL Server string column.

If you've already loaded the data and need to adjust it:

• For display only (datetime columns):
  Run this query to get dates in your desired format:

  SELECT 
    FORMAT(YourDateColumn, 'dd/MM/yyyy HH:mm:ss') AS FormattedDate
  FROM YourTable;
  

• To convert and store as string:
  Execute these SQL commands to add a formatted column and populate it:

  ALTER TABLE YourTable ADD FormattedDate VARCHAR(19);
  UPDATE YourTable 
  SET FormattedDate = FORMAT(YourDateColumn, 'dd/MM/yyyy HH:mm:ss');
  -- Optional: Drop the original date column and rename the new one
  -- ALTER TABLE YourTable DROP COLUMN YourDateColumn;
  -- EXEC sp_rename 'YourTable.FormattedDate', 'YourDateColumn', 'COLUMN';
  

内容的提问来源于stack exchange，提问作者Jana N