SQL Server：合并DATA与ORA字段并转换为datetime（含意大利语月份）

Got it, let's tackle this problem step by step. Since SQL Server doesn't natively recognize Italian month names in datetime conversions unless the session language is set appropriately, we've got two solid approaches here depending on your constraints.

This is the most straightforward method—when your session language is set to Italian, SQL Server's CONVERT/TRY_CONVERT functions can correctly parse Italian month names.

Steps: #

• Save your original session language (optional, to avoid disrupting subsequent operations)
• Switch the session language to Italian
• Concatenate the DATA and ORA fields, then convert the result to datetime
• Restore your original language if needed

Example Code: #

-- Save original session language (optional)
DECLARE @OriginalLang NVARCHAR(100);
SELECT @OriginalLang = @@LANGUAGE;

-- Set session language to Italian
SET LANGUAGE Italiano;

-- Convert and insert into target table
INSERT INTO TargetTable(DateTimeColumn)
SELECT TRY_CONVERT(DATETIME, CONCAT(DATA, ' ', ORA))
FROM SourceTable
WHERE TRY_CONVERT(DATETIME, CONCAT(DATA, ' ', ORA)) IS NOT NULL; -- Filter out invalid entries

-- Restore original session language (optional)
SET LANGUAGE @OriginalLang;

Notes: #

• TRY_CONVERT returns NULL instead of throwing an error if conversion fails, so you can safely filter out bad data
• SET LANGUAGE is a session-level setting—only affects your current connection, not the server's global configuration

If you can't modify the session language (e.g., in a multi-language environment), you can map Italian month names to English ones (which SQL Server recognizes natively) before conversion.

Example Code with CTE Mapping: #

-- Define Italian-to-English month mappings
WITH ItalianToEnglishMonths AS (
    SELECT 'Gennaio' AS ItaMonth, 'January' AS EngMonth UNION ALL
    SELECT 'Febbraio', 'February' UNION ALL
    SELECT 'Marzo', 'March' UNION ALL
    SELECT 'Aprile', 'April' UNION ALL
    SELECT 'Maggio', 'May' UNION ALL
    SELECT 'Giugno', 'June' UNION ALL
    SELECT 'Luglio', 'July' UNION ALL
    SELECT 'Agosto', 'August' UNION ALL
    SELECT 'Settembre', 'September' UNION ALL
    SELECT 'Ottobre', 'October' UNION ALL
    SELECT 'Novembre', 'November' UNION ALL
    SELECT 'Dicembre', 'December'
)
-- Convert and insert into target table
INSERT INTO TargetTable(DateTimeColumn)
SELECT TRY_CONVERT(DATETIME, CONCAT(REPLACE(s.DATA, m.ItaMonth, m.EngMonth), ' ', s.ORA))
FROM SourceTable s
JOIN ItalianToEnglishMonths m ON s.DATA LIKE '%' + m.ItaMonth + '%'
WHERE TRY_CONVERT(DATETIME, CONCAT(REPLACE(s.DATA, m.ItaMonth, m.EngMonth), ' ', s.ORA)) IS NOT NULL;

Alternative: Nested REPLACE (No JOIN Needed) #

If you prefer a more direct approach without CTEs, you can nest REPLACE functions to swap out each Italian month:

INSERT INTO TargetTable(DateTimeColumn)
SELECT TRY_CONVERT(DATETIME, 
    CONCAT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            DATA, 
            'Gennaio', 'January'), 'Febbraio', 'February'), 'Marzo', 'March'),
            'Aprile', 'April'), 'Maggio', 'May'), 'Giugno', 'June'),
            'Luglio', 'July'), 'Agosto', 'August'), 'Settembre', 'September'),
            'Ottobre', 'October'), 'Novembre', 'November'), 'Dicembre', 'December'),
        ' ', ORA
    )
)
FROM SourceTable
WHERE TRY_CONVERT(DATETIME, 
    CONCAT(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            DATA, 
            'Gennaio', 'January'), 'Febbraio', 'February'), 'Marzo', 'March'),
            'Aprile', 'April'), 'Maggio', 'May'), 'Giugno', 'June'),
            'Luglio', 'July'), 'Agosto', 'August'), 'Settembre', 'September'),
            'Ottobre', 'October'), 'Novembre', 'November'), 'Dicembre', 'December'),
        ' ', ORA
    )
) IS NOT NULL;

Before running the INSERT, always verify your conversions with a SELECT query to catch any issues:

-- Validate for Scheme 1
SET LANGUAGE Italiano;
SELECT 
    DATA, 
    ORA, 
    CONCAT(DATA, ' ', ORA) AS CombinedString,
    TRY_CONVERT(DATETIME, CONCAT(DATA, ' ', ORA)) AS ConvertedDateTime
FROM SourceTable;

-- Validate for Scheme 2
WITH ItalianToEnglishMonths AS (
    SELECT 'Gennaio' AS ItaMonth, 'January' AS EngMonth UNION ALL
    SELECT 'Febbraio', 'February' UNION ALL
    SELECT 'Marzo', 'March' UNION ALL
    SELECT 'Aprile', 'April' UNION ALL
    SELECT 'Maggio', 'May' UNION ALL
    SELECT 'Giugno', 'June' UNION ALL
    SELECT 'Luglio', 'July' UNION ALL
    SELECT 'Agosto', 'August' UNION ALL
    SELECT 'Settembre', 'September' UNION ALL
    SELECT 'Ottobre', 'October' UNION ALL
    SELECT 'Novembre', 'November' UNION ALL
    SELECT 'Dicembre', 'December'
)
SELECT 
    DATA, 
    ORA, 
    CONCAT(REPLACE(s.DATA, m.ItaMonth, m.EngMonth), ' ', s.ORA) AS CombinedString,
    TRY_CONVERT(DATETIME, CONCAT(REPLACE(s.DATA, m.ItaMonth, m.EngMonth), ' ', s.ORA)) AS ConvertedDateTime
FROM SourceTable s
JOIN ItalianToEnglishMonths m ON s.DATA LIKE '%' + m.ItaMonth + '%';

内容的提问来源于stack exchange，提问作者Paolo Baeli