Time formatting in SQL Server can sometimes be tricky, especially when you need to switch between 24-hour and 12-hour (AM/PM) formats. In this guide, we’ll explore various methods to format time in SQL Server, with a focus on converting to AM/PM display.

Understanding Time Formats in SQL Server

SQL Server stores time data in 24-hour format by default. However, for better readability and user-friendly displays, you might need to convert it to 12-hour format with AM/PM indicators. Let’s dive into different approaches to achieve this.

Method 1: Using CONVERT Function

The CONVERT function is one of the most common ways to format time in SQL Server. Here’s how you can use it:

-- Current time in AM/PM format
SELECT CONVERT(varchar(12), GETDATE(), 100) AS [Time_AMPM]
-- Output: Jan 15 10:30AM

-- Time only with AM/PM
SELECT CONVERT(varchar(12), GETDATE(), 108) AS [24Hour_Time],
       CONVERT(varchar(12), GETDATE(), 100) AS [AMPM_Time]
-- Output: 10:30:15    10:30AM

Method 2: Using FORMAT Function (SQL Server 2012 and later)

The FORMAT function provides more flexibility and is easier to read:

-- Basic AM/PM formatting
SELECT FORMAT(GETDATE(), 'hh:mm tt') AS [Time_AMPM]
-- Output: 10:30 AM

-- Custom formatting with seconds
SELECT FORMAT(GETDATE(), 'hh:mm:ss tt') AS [Time_AMPM_With_Seconds]
-- Output: 10:30:15 AM

-- Full date with AM/PM time
SELECT FORMAT(GETDATE(), 'MMM dd yyyy hh:mm tt') AS [DateTime_AMPM]
-- Output: Jan 15 2024 10:30 AM

Method 3: Working with Time Data Type

When working specifically with TIME data type:

DECLARE @TimeValue TIME = '13:45:30'

-- Convert to AM/PM format
SELECT FORMAT(@TimeValue, 'hh:mm tt') AS [AMPM_Time],
       FORMAT(@TimeValue, 'hh:mm:ss tt') AS [AMPM_Time_With_Seconds]
-- Output: 01:45 PM    01:45:30 PM

Real-World Example: Employee Shift Schedule

Here’s a practical example showing how to display employee shift timings in AM/PM format:

-- Create a sample employee shifts table
CREATE TABLE EmployeeShifts (
    EmployeeID INT,
    EmployeeName VARCHAR(50),
    ShiftStart TIME,
    ShiftEnd TIME
)

-- Insert sample data
INSERT INTO EmployeeShifts VALUES
(1, 'John Doe', '09:00', '17:00'),
(2, 'Jane Smith', '14:00', '22:00'),
(3, 'Mike Johnson', '23:00', '07:00')

-- Query with formatted times
SELECT 
    EmployeeName,
    FORMAT(ShiftStart, 'hh:mm tt') AS ShiftStartTime,
    FORMAT(ShiftEnd, 'hh:mm tt') AS ShiftEndTime
FROM EmployeeShifts

/* Output:
EmployeeName    ShiftStartTime    ShiftEndTime
John Doe        09:00 AM          05:00 PM
Jane Smith      02:00 PM          10:00 PM
Mike Johnson    11:00 PM          07:00 AM
*/

Tips and Best Practices

1. Choose the Right Method:
   • Use FORMAT for better readability and flexibility
   • Use CONVERT for better performance in large datasets
2. Time Zone Considerations:
   • Always be mindful of time zones when formatting times
   • Consider storing UTC time and converting at display time
3. Performance Impact:
   • FORMAT is more resource-intensive than CONVERT
   • For large datasets, consider formatting at the application level

Common Format Specifiers

Here’s a quick reference for common format specifiers:

-- Various formatting examples
SELECT 
    FORMAT(GETDATE(), 'hh:mm tt') AS [Basic_Time],      -- 10:30 AM
    FORMAT(GETDATE(), 'h:mm tt') AS [No_Leading_Zero],  -- 10:30 AM
    FORMAT(GETDATE(), 'hh:mm:ss tt') AS [With_Seconds], -- 10:30:15 AM
    FORMAT(GETDATE(), 'HH:mm') AS [24Hour_Format]       -- 22:30

Formatting time in SQL Server to display AM/PM is straightforward once you understand the available functions and their usage. Whether you choose CONVERT or FORMAT depends on your specific needs regarding readability, performance, and compatibility. Remember to consider your application’s requirements and choose the method that best balances performance and functionality.

Remember that while FORMAT provides more flexible and readable options, it might impact performance with large datasets. For optimal performance in production environments, consider handling time formatting at the application level when dealing with large amounts of data.