SQL Server Format Time AM PM

When working with time data in SQL Server, it's often necessary to format time in a specific way, such as displaying it in 12-hour format with AM/PM indicators. SQL Server provides several functions to manipulate and format date and time data, including the `FORMAT` function, `CONVERT` function, and `CAST` function. In this article, we'll explore how to format time in SQL Server to display it in 12-hour format with AM/PM indicators.

Using the FORMAT Function

The FORMAT function is a versatile function in SQL Server that allows you to format date and time values in a variety of ways. To format time in 12-hour format with AM/PM indicators, you can use the FORMAT function with the ‘hh:mm tt’ format specifier. Here’s an example:

DECLARE @Time DATETIME = '2022-01-01 14:30:00';
SELECT FORMAT(@Time, 'hh:mm tt') AS FormattedTime;

This will output: `02:30 PM`

Format Specifiers for Time

Here are some common format specifiers for time that you can use with the FORMAT function:

• `hh`: Hour (01-12)
• `mm`: Minute (00-59)
• `ss`: Second (00-59)
• `tt`: AM/PM designator

For example, to format time in 12-hour format with minutes and seconds, you can use the 'hh:mm:ss tt' format specifier:

DECLARE @Time DATETIME = '2022-01-01 14:30:00';
SELECT FORMAT(@Time, 'hh:mm:ss tt') AS FormattedTime;

This will output: `02:30:00 PM`

Using the CONVERT Function

The CONVERT function is another way to format date and time data in SQL Server. To format time in 12-hour format with AM/PM indicators, you can use the CONVERT function with the style parameter set to 100 or 120. Here’s an example:

DECLARE @Time DATETIME = '2022-01-01 14:30:00';
SELECT CONVERT(VARCHAR(20), @Time, 100) AS FormattedTime;

This will output: `02:30PM`

Note that the `CONVERT` function returns a string value, so you may need to trim or format the output to remove unwanted characters.

Style Parameters for CONVERT

Here are some common style parameters for the CONVERT function:

• `100`: `mon dd yyyy hh:miAM/PM`
• `120`: `yyyy-mm-dd hh:mi:ss(24h)`

For example, to format time in 24-hour format with minutes and seconds, you can use the `CONVERT` function with the `style` parameter set to 120:

DECLARE @Time DATETIME = '2022-01-01 14:30:00';
SELECT CONVERT(VARCHAR(20), @Time, 120) AS FormattedTime;

This will output: `2022-01-01 14:30:00`