SQL Server Get AM or PM from Date

Extracting AM/PM indicators from datetime values becomes crucial for business reporting and application development. Understanding how to properly extract and format AM/PM indicators from SQL Server datetime values is an essential skill. In this comprehensive article, I’ll walk you through every method for this.

SQL Server Get AM or PM from Date

Before diving into AM/PM extraction methods, let me explain the foundation you need to understand the core datetime data types in SQL Server.

The key insight I’ve gained from troubleshooting systems for companies in Boston, Denver, and Portland is that AM/PM extraction only makes sense when working with data types that include time components.

AM/PM formatting is crucial for:

• User Interface Display: Making time data readable for end users
• Business Reports: Creating executive dashboards with intuitive time formats
• Compliance Requirements: Meeting industry-specific time display standards
• Customer-Facing Applications: Ensuring user-friendly time representations
• Integration Projects: Converting between different system time formats

Method 1: Using the CONVERT Function with Style Codes

The CONVERT function has been my go-to solution for AM/PM extraction throughout my career.

Example 1

We can execute the queries below to extract using CONVERT with style 100.

-- Basic AM/PM extraction using CONVERT with style 100
SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS DateTimeWithAMPM

-- Extracting only time with AM/PM using style 100
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7) AS TimeWithAMPM

After executing the above query, I got the expected output as shown in the screenshot below.

Common CONVERT Style Codes for AM/PM

Based on my implementations across various industries, here are the most useful style codes:

Advanced CONVERT Techniques

During my work with a pharmaceutical company in San Francisco, I developed these advanced techniques:

Extracting Only AM/PM Indicator:

Example 2

We can also query using the RIGHT and CONVERT methods.

-- Method 1: Using RIGHT and CONVERT
SELECT RIGHT(CONVERT(VARCHAR(20), StartTime, 100), 2) AS AMPM_Indicator from ShiftSchedule

After executing the above query, I got the expected output as shown in the screenshot below.

Custom Time Formatting:

Example 3

Creating custom time display formats using the below query.

-- Creating custom time display formats
SELECT 
    StartTime,
    CONVERT(VARCHAR(8), StartTime, 108) + ' ' + 
    RIGHT(CONVERT(VARCHAR(20), StartTime, 100), 2) AS FormattedClockIn
FROM ShiftSchedule

After executing the above query, I got the expected output as shown in the screenshot below.

Method 2: Using FORMAT Function (SQL Server 2012+)

Modern Approach with FORMAT

When Microsoft introduced the FORMAT function in SQL Server 2012, it revolutionized how I handle datetime formatting. This function significantly simplifies our reporting queries.

-- Basic AM/PM formatting with FORMAT
SELECT FORMAT(GETDATE(), 'hh:mm tt') AS TimeWithAMPM

-- Full datetime with AM/PM
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy hh:mm tt') AS FullDateTime

After executing the above query, I got the expected output as shown in the screenshot below.

FORMAT Function Advantages

These are the key advantages:

• Intuitive Syntax: Easier to read and maintain
• Flexible Formatting: More format options available
• Culture-Aware: Automatic localization support
• Consistent Results: Predictable output across different scenarios

FORMAT Pattern Reference

Method 3: Custom Functions and Advanced Techniques

Creating Reusable Solutions

Custom Scalar Function for AM/PM Extraction:

CREATE FUNCTION dbo.GetAMPMIndicator(@DateTime DATETIME)
RETURNS VARCHAR(2)
AS
BEGIN
    RETURN RIGHT(CONVERT(VARCHAR(20), @DateTime, 100), 2)
END

After executing the above query, I got the expected output as shown in the screenshot below.

Enhanced Time Formatting Function:

CREATE FUNCTION dbo.FormatTimeWithAMPM(@DateTime DATETIME, @ShowSeconds BIT = 0)
RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @Result VARCHAR(15)
    
    IF @ShowSeconds = 1
        SET @Result = FORMAT(@DateTime, 'h:mm:ss tt')
    ELSE
        SET @Result = FORMAT(@DateTime, 'h:mm tt')
        
    RETURN @Result
END

After executing the above query, I got the expected output as shown in the screenshot below.

Performance Considerations

Through extensive performance testing for a retail chain with locations from California to Florida, I’ve learned these optimization principles:

Performance Comparison:

Best Practices for Performance:

• Use CONVERT for High-Volume Queries: When processing millions of records
• Cache Function Results: Store frequently accessed formatted values
• Index Considerations: Be careful with WHERE clauses on formatted dates
• Batch Processing: Handle large datasets in smaller chunks

Handling Different Scenarios

Working with NULL Values

In my experience with data migration projects across different industries, NULL handling is crucial:

-- Safe AM/PM extraction with NULL handling
SELECT 
    appointment_id,
    CASE 
        WHEN appointment_time IS NULL THEN 'Not Scheduled'
        ELSE FORMAT(appointment_time, 'h:mm tt')
    END AS AppointmentTime
FROM patient_appointments

Time Zone Considerations

-- Converting UTC to local time with AM/PM
SELECT 
    shipment_id,
    FORMAT(
        DATEADD(hour, -8, utc_delivery_time), 
        'MM/dd/yyyy h:mm tt'
    ) AS PST_DeliveryTime,
    FORMAT(
        DATEADD(hour, -5, utc_delivery_time), 
        'MM/dd/yyyy h:mm tt'  
    ) AS EST_DeliveryTime
FROM shipment_tracking

Advanced Applications and Real-World Use Cases

Report Generation

Daily Activity Report:

-- Generating activity summary with readable time formats
SELECT 
    user_id,
    COUNT(*) AS ActivityCount,
    FORMAT(MIN(activity_time), 'h:mm tt') AS FirstActivity,
    FORMAT(MAX(activity_time), 'h:mm tt') AS LastActivity,
    DATEDIFF(minute, MIN(activity_time), MAX(activity_time)) AS ActiveMinutes
FROM user_activity_log
WHERE activity_date = CAST(GETDATE() AS DATE)
GROUP BY user_id

Appointment Scheduling Display:

-- Patient appointment listing with user-friendly times
SELECT 
    patient_name,
    doctor_name,
    FORMAT(appointment_datetime, 'dddd, MMM dd') AS AppointmentDate,
    FORMAT(appointment_datetime, 'h:mm tt') AS AppointmentTime,
    appointment_type
FROM patient_appointments
WHERE appointment_datetime >= GETDATE()
ORDER BY appointment_datetime

Conclusion:

Knowing AM/PM extraction is about more than just knowing the syntax. It’s about understanding your data, your users, and your system’s performance requirements.

The key takeaways I want you to remember are:

Choose the Right Method for Your Scenario:

• Use CONVERT() for high-performance, high-volume operations
• Use FORMAT() for user-friendly applications and reports
• Create custom functions for complex, reusable logic

Always Consider Performance:

• Filter data before formatting
• Cache frequently used formatted values
• Avoid formatting functions in WHERE clauses

You may also like the following articles:

• SQL Server Date Minus 6 Months
• SQL Server Truncate Date to Minute
• How To Convert Date To String In SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.