When you’re building reports in SQL Server, there’s a good chance you’ll need to display dates in a more human-readable format than the ISO 8601 standard that will likely be returned in the absence of any formatting. Nobody wants to see “2024-03-15” when “March” or “Friday” would make the report instantly clearer. SQL Server gives you several ways to extract and format these labels, and knowing which approach fits your situation can save you time and make your queries cleaner.
The Basic Tools: DATENAME() and FORMAT()
The most straightforward way to get month and weekday names is with the DATENAME() function. It works consistently, and does pretty much exactly what you’d expect:
SELECT
DATENAME(MONTH, GETDATE()) AS MonthName,
DATENAME(WEEKDAY, GETDATE()) AS WeekdayName;Result:
MonthName WeekdayName
--------- -----------
November Sunday
This gives you full names like “November” and “Sunday”.
If you need abbreviated versions, you can use the FORMAT() function, though it can be a bit slower for large datasets:
SELECT
FORMAT(GETDATE(), 'MMM') AS MonthAbbr,
FORMAT(GETDATE(), 'ddd') AS WeekdayAbbr;Result:
MonthAbbr WeekdayAbbr
--------- -----------
Nov Sun
For most reporting scenarios, DATENAME() is the the way to go because it’s fast and reliable. The FORMAT() function is more flexible when you need custom date formatting, but that flexibility comes with a performance cost.
One benefit that FORMAT() has over DATENAME() though, is its locale aware formatting capabilities. You can specify a locale right there within the function, which may be just what you need in some cases.
Example
Let’s say you’re tracking when support tickets are created and you want to see patterns by day of the week and month. Here’s a simple dataset we can work with:
-- Create sample data
CREATE TABLE SupportTickets (
TicketID INT,
CreatedDate DATE,
Priority VARCHAR(10),
ResolvedHours INT
);
INSERT INTO SupportTickets (TicketID, CreatedDate, Priority, ResolvedHours)
VALUES
(1, '2024-01-15', 'High', 4),
(2, '2024-01-18', 'Medium', 12),
(3, '2024-02-03', 'Low', 48),
(4, '2024-02-14', 'High', 2),
(5, '2024-03-07', 'Medium', 8),
(6, '2024-03-22', 'High', 5),
(7, '2024-03-29', 'Low', 36),
(8, '2024-04-11', 'Medium', 15),
(9, '2024-04-18', 'High', 3),
(10, '2024-05-02', 'Low', 24);Now let’s create a report showing ticket volume by month with readable labels:
SELECT
DATENAME(MONTH, CreatedDate) AS Month,
MONTH(CreatedDate) AS MonthNumber,
COUNT(*) AS TicketCount,
AVG(ResolvedHours) AS AvgResolutionTime
FROM SupportTickets
GROUP BY DATENAME(MONTH, CreatedDate), MONTH(CreatedDate)
ORDER BY MonthNumber;Result:
Month MonthNumber TicketCount AvgResolutionTime
-------- ----------- ----------- -----------------
January 1 2 8
February 2 2 25
March 3 3 16
April 4 2 9
May 5 1 24
Notice we’re including MONTH(CreatedDate) in both the GROUP BY and SELECT clauses. This gives us a numeric value we can use for proper sorting, because alphabetically “April” comes before “January”, which isn’t what we want.
Handling Day-of-Week Analysis
When you want to see patterns by weekday, things get a bit trickier because weeks don’t follow the same sorting logic as months. You’ll want to control whether Monday or Sunday is considered the first day:
SELECT
DATENAME(WEEKDAY, CreatedDate) AS Weekday,
DATEPART(WEEKDAY, CreatedDate) AS WeekdayNumber,
COUNT(*) AS TicketCount,
SUM(CASE WHEN Priority = 'High' THEN 1 ELSE 0 END) AS HighPriorityCount
FROM SupportTickets
GROUP BY DATENAME(WEEKDAY, CreatedDate), DATEPART(WEEKDAY, CreatedDate)
ORDER BY WeekdayNumber;Result:
Weekday WeekdayNumber TicketCount HighPriorityCount
--------- ------------- ----------- -----------------
Monday 2 1 1
Wednesday 4 1 1
Thursday 5 5 1
Friday 6 2 1
Saturday 7 1 0
The DATEPART(WEEKDAY, ...) value depends on your server’s DATEFIRST setting.
If you need consistent ordering regardless of server settings, you can use DATEDIFF() with a reference date:
SELECT
DATENAME(WEEKDAY, CreatedDate) AS Weekday,
(DATEDIFF(DAY, '19000101', CreatedDate) % 7) AS DayPosition,
COUNT(*) AS TicketCount
FROM SupportTickets
GROUP BY DATENAME(WEEKDAY, CreatedDate), (DATEDIFF(DAY, '19000101', CreatedDate) % 7)
ORDER BY DayPosition;Result:
Weekday DayPosition TicketCount
--------- ----------- -----------
Monday 0 1
Wednesday 2 1
Thursday 3 5
Friday 4 2
Saturday 5 1
This calculates days since a known Monday (January 1, 1900) and uses modulo to get a consistent 0-6 value.
Dynamic Pivoting for Calendar Views
Sometimes you want a matrix view where months are columns and weekdays are rows, or vice versa. Here’s a straightforward pivot showing ticket counts:
SELECT
DATENAME(WEEKDAY, CreatedDate) AS Weekday,
SUM(CASE WHEN MONTH(CreatedDate) = 1 THEN 1 ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(CreatedDate) = 2 THEN 1 ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(CreatedDate) = 3 THEN 1 ELSE 0 END) AS Mar,
SUM(CASE WHEN MONTH(CreatedDate) = 4 THEN 1 ELSE 0 END) AS Apr,
SUM(CASE WHEN MONTH(CreatedDate) = 5 THEN 1 ELSE 0 END) AS May
FROM SupportTickets
GROUP BY DATENAME(WEEKDAY, CreatedDate), DATEPART(WEEKDAY, CreatedDate)
ORDER BY DATEPART(WEEKDAY, CreatedDate);Result:
Weekday Jan Feb Mar Apr May
--------- --- --- --- --- ---
Monday 1 0 0 0 0
Wednesday 0 1 0 0 0
Thursday 1 0 1 2 1
Friday 0 0 2 0 0
Saturday 0 1 0 0 0
This gives you a quick view of which combinations of weekday and month see the most activity.
Performance Considerations
When you’re working with larger datasets, keep in mind that DATENAME() and DATEPART() functions can prevent index usage if they’re applied to indexed date columns in WHERE clauses. If you’re filtering by month or weekday, it’s better to use date range comparisons:
-- Less efficient
WHERE DATENAME(MONTH, CreatedDate) = 'March'
-- More efficient
WHERE CreatedDate >= '2024-03-01' AND CreatedDate < '2024-04-01'That said, using these functions in SELECT and GROUP BY clauses is perfectly fine for most reporting needs.
In summary, month and weekday labels make your reports instantly understandable, and with the right sorting strategy, you can present the data in a logical order that makes sense to your audience.