SQL Server 2008 R2特定时间格式求助：如何输出06:00:00 p.m.

Absolutely! Since SQL Server 2008 R2 doesn't support the handy FORMAT() function that would simplify this task, we can build your desired 06:00:00 p.m. format using a mix of datepart extraction and string manipulation. Here are two practical approaches:

1. Ad-Hoc Query for One-Time Use #

If you only need to format a time/datetime column in a single query, use this inline logic:

SELECT 
    -- Convert 24-hour to 12-hour format (handles 00:00 as 12 a.m. and 12:00 as 12 p.m.)
    CASE 
        WHEN DATEPART(HOUR, YourDateTimeColumn) = 0 THEN '12'
        WHEN DATEPART(HOUR, YourDateTimeColumn) > 12 THEN CAST(DATEPART(HOUR, YourDateTimeColumn) - 12 AS VARCHAR(2))
        ELSE CAST(DATEPART(HOUR, YourDateTimeColumn) AS VARCHAR(2))
    END + ':' +
    -- Pad minutes with leading zero (e.g., 5 becomes 05)
    RIGHT('0' + CAST(DATEPART(MINUTE, YourDateTimeColumn) AS VARCHAR(2)), 2) + ':' +
    -- Pad seconds with leading zero
    RIGHT('0' + CAST(DATEPART(SECOND, YourDateTimeColumn) AS VARCHAR(2)), 2) + ' ' +
    -- Add a.m./p.m. suffix
    CASE 
        WHEN DATEPART(HOUR, YourDateTimeColumn) < 12 THEN 'a.m.'
        ELSE 'p.m.'
    END AS FormattedTime
FROM YourTableName;

Just replace YourDateTimeColumn with your actual datetime/time column name, and YourTableName with the table you're querying.

2. Reusable Custom Function #

If you need this format across multiple queries, creating a user-defined function will save you repeated work:

CREATE FUNCTION dbo.GetFormattedAMPMTime (@InputTime TIME)
RETURNS VARCHAR(15)
AS
BEGIN
    -- Extract individual time components
    DECLARE @Hour INT = DATEPART(HOUR, @InputTime);
    DECLARE @Minute VARCHAR(2) = RIGHT('0' + CAST(DATEPART(MINUTE, @InputTime) AS VARCHAR(2)), 2);
    DECLARE @Second VARCHAR(2) = RIGHT('0' + CAST(DATEPART(SECOND, @InputTime) AS VARCHAR(2)), 2);
    
    -- Determine a.m./p.m. and convert hour to 12-hour format
    DECLARE @Period VARCHAR(4) = CASE WHEN @Hour < 12 THEN 'a.m.' ELSE 'p.m.' END;
    DECLARE @12Hour VARCHAR(2) = CASE 
                                    WHEN @Hour = 0 THEN '12'
                                    WHEN @Hour > 12 THEN CAST(@Hour - 12 AS VARCHAR(2))
                                    ELSE CAST(@Hour AS VARCHAR(2))
                                END;

    -- Combine all parts into the desired format
    RETURN @12Hour + ':' + @Minute + ':' + @Second + ' ' + @Period;
END;

To use the function, simply call it with your time column:

SELECT dbo.GetFormattedAMPMTime(YourTimeColumn) AS FormattedTime FROM YourTableName;

Both methods will output exactly the format you need—like 06:00:00 p.m. for 18:00:00, or 12:30:45 a.m. for 00:30:45.

内容的提问来源于stack exchange，提问作者J. Rodríguez