How to Custom Format DateTime in SQL Server to 'DDMMM' and 'HHMMT'?

Custom Date/Time Formatting in SQL Server

Objective: Format a Datetime field into two additional columns: 'DDMMM' and 'HHMMT' (where 'T' represents 'A' for a.m. and 'P' for p.m.).

Scenario: Given a Datetime field with the format 'YYYY-MM-DD HH:MM:SS.S', the desired output is:

• '12OCT' for the 'DDMMM' column
• '0119P' for the 'HHMMT' column

Solution:

Instead of using a stored procedure, consider using a function to encapsulate the logic.

DECLARE @myTime AS DATETIME;

SET @myTime = GETDATE();

SELECT @myTime AS OriginalTime;

SELECT DATENAME(DAY, @myTime) + SUBSTRING(UPPER(DATENAME(MONTH, @myTime)), 0, 4) AS 'DDMMM';

Output:

OriginalTime          DDMMM
---------------------- --------------------
2023-03-08 13:19:12.0 08MAR

Note:

• Use string combinations within quotes ('') to concatenate multiple strings into the result.
• Avoid character/string operations on Datetime data types as they can impact performance.

Additional Tip:

Reference useful date conversion functions for various scenarios:

-- Getting Parts of a DateTime
SELECT FLOOR(CAST(GETDATE() AS FLOAT)) AS DateOnly, GETDATE() - FLOOR(CAST(GETDATE() AS FLOAT)) AS TimeOnly;

-- Relative Dates
SELECT GETDATE() AS CurrentTime, DATEADD(dd, 1, GETDATE()) AS Tomorrow;
SELECT GETDATE() AS CurrentTime, DATEADD(hh, 1, GETDATE()) AS InOneHour;

-- Yearly
SELECT DATEADD(yy, -1, GETDATE()) AS LastYear, DATEADD(yy, 1, GETDATE()) AS NextYear;
SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS CurrentYearStart, DATEADD(ms, -4, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()) + 1, 0)) AS CurrentYearEnd;

The above is the detailed content of How to Custom Format DateTime in SQL Server to 'DDMMM' and 'HHMMT'?. For more information, please follow other related articles on the PHP Chinese website!