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
-- 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
-- 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
- Choose the Right Method:
- Use
Use Time Zone Considerations:
- Always be mindful of time zones when formatting times
- Consider storing UTC time and converting at display time
- Performance Impact:
- 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
Remember that while
| Read More Topics |
| Basic SQL knowledge for programmer and developers |
| Oracle SQL cheat sheet |
| SQL data definition and data types |
