How to Customize Date/Time Formatting in SQL Server Using a Function?

Customized Date/Time Formatting in SQL Server

In the world of SQL Server, formatting dates and times to suit specific requirements can be an essential task. This is particularly true when dealing with data that needs to be displayed in a user-friendly or standardized manner. One such example involves converting a datetime field into two additional fields that adhere to specific formatting conventions.

Extraction of DDMMM and HHMMT Fields

Consider a datetime field with the format 'YYYY-MM-DD HH:MM:SS.S'. The objective is to extract two new fields from this field:

• DDMMM: Representing the day and month in the format "DDMMM," such as "12OCT" for October 12th.
• HHMMT: Displaying the time in the format "HHMMT," where "T" denotes 'A' for a.m. and 'P' for p.m. For instance, 01:19 PM would be represented as "0119P."

Function-Based Approach

To achieve this conversion effectively, leveraging a function is recommended instead of a stored procedure. This allows for greater flexibility and code reusability.

CREATE FUNCTION FormatDateTime (@DatetimeField DATETIME)
RETURNS VARCHAR(50)
BEGIN
    DECLARE @FormattedValue VARCHAR(50)

    SELECT @FormattedValue = DATENAME(DAY, @DatetimeField) + SUBSTRING(UPPER(DATENAME(MONTH, @DatetimeField)), 0, 4) + ' ' +
                            CASE WHEN DATENAME(HOUR, @DatetimeField) < 12 THEN 'A'
                                 ELSE 'P'
                            END
                            + RIGHT(CAST(DATENAME(HOUR, @DatetimeField) AS VARCHAR), 2)
                            + CAST(DATENAME(MINUTE, @DatetimeField) AS VARCHAR(2))

    RETURN @FormattedValue
END
GO

Demonstration

To demonstrate the function's functionality, consider the following query:

SELECT FormatDateTime(GETDATE())

This query would return the formatted datetime as "14OCT 0119P," meeting the desired requirements.

Conclusion

By employing a function-based approach and utilizing the DATENAME function, we have effectively achieved customized date/time formatting in SQL Server. This allows us to extract and present data in formats that align with specific business requirements and user preferences. Moreover, using numerical operations for date manipulation ensures optimal performance.

The above is the detailed content of How to Customize Date/Time Formatting in SQL Server Using a Function?. For more information, please follow other related articles on the PHP Chinese website!