Displaying Abbreviated and Full Day Names for Reports in SQL Server

by

When building reports in SQL Server, dates are probably one of the most common pieces of data you’ll deal with. Sometimes a report needs the full day name like “Monday”, while in other cases a short form like “Mon” is preferred, often to save space. Luckily, SQL Server has built-in functionality to handle both, without having to manually map numbers to names.

Let’s look at how we can display abbreviated and full day names in queries so that our reports are nice and easy to read.

Setting Up Some Test Data

Let’s create a small table of scheduled tasks with different dates. We’ll insert a few rows so we can immediately run queries:

-- Drop table if it exists for reruns
DROP TABLE IF EXISTS dbo.TaskSchedule;
GO

-- Create a simple table
CREATE TABLE dbo.TaskSchedule
(
    TaskID INT IDENTITY PRIMARY KEY,
    TaskName NVARCHAR(100),
    ScheduledDate DATE
);

-- Insert some sample data
INSERT INTO dbo.TaskSchedule (TaskName, ScheduledDate)
VALUES
('Database Backup', '2025-10-01'),
('Index Maintenance', '2025-10-02'),
('ETL Job', '2025-10-03'),
('Monthly Report', '2025-10-05'),
('System Patch', '2025-10-07');

Now we’ve got a table with a handful of dates to work with.

Extracting Full and Abbreviated Day Names

SQL Server’s DATENAME() function returns the full textual name of the day, while FORMAT() can handle both abbreviated and full names depending on the format string you use.

Here’s a query to display both:

SELECT 
    TaskID,
    TaskName,
    ScheduledDate,
    DATENAME(WEEKDAY, ScheduledDate) AS FullDayName,
    FORMAT(ScheduledDate, 'ddd') AS AbbreviatedDayName
FROM dbo.TaskSchedule
ORDER BY ScheduledDate;

Result:

TaskID  TaskName             ScheduledDate  FullDayName   AbbreviatedDayName
------  ------------------   -------------  -----------   ------------------ 
1       Database Backup      2025-10-01     Wednesday     Wed
2       Index Maintenance    2025-10-02     Thursday      Thu
3       ETL Job              2025-10-03     Friday        Fri
4       Monthly Report       2025-10-05     Sunday        Sun
5       System Patch         2025-10-07     Tuesday       Tue

This makes reports more readable since users can quickly recognize days of the week at a glance.

Here’s how it works:

  • DATENAME(WEEKDAY, ScheduledDate): Returns the complete day name, e.g. “Wednesday”.
  • FORMAT(ScheduledDate, 'ddd'): Uses .NET style formatting inside SQL Server. ddd gives you the three-letter abbreviation like “Wed”, while dddd would give the full name.

So if you prefer consistency, you could even replace DATENAME with FORMAT(..., 'dddd'). Both options work fine. I only used two functions here to demonstrate that we have alternatives.

Localization

When working with international reports, you might need day names in languages other than English. The FORMAT() function makes this possible with its optional culture parameter. This allows you to display day names in French, Spanish, German, or virtually any supported locale.

Example

We can reuse the TaskSchedule table from before. Here’s a query that shows full and abbreviated day names in various languages:

SELECT
    TaskID,
    TaskName,
    ScheduledDate,
    FORMAT(ScheduledDate, 'dddd', 'fr-FR') AS FullDayName_FR,
    FORMAT(ScheduledDate, 'ddd', 'fr-FR') AS AbbreviatedDayName_FR,
    FORMAT(ScheduledDate, 'dddd', 'es-ES') AS FullDayName_ES,
    FORMAT(ScheduledDate, 'ddd', 'es-ES') AS AbbreviatedDayName_ES
FROM dbo.TaskSchedule
ORDER BY ScheduledDate;

Output:

TaskID  TaskName           ScheduledDate  FullDayName_FR  AbbreviatedDayName_FR  FullDayName_ES  AbbreviatedDayName_ES
1       Database Backup    2025-10-01     mercredi        mer.                    miércoles       mié
2       Index Maintenance  2025-10-02     jeudi           jeu.                    jueves          jue
3       ETL Job            2025-10-03     vendredi        ven.                    viernes         vie
4       Monthly Report     2025-10-05     dimanche        dim.                    domingo         dom
5       System Patch       2025-10-07     mardi           mar.                    martes          mar

It works like this:

  • The third parameter of FORMAT() is the culture code, e.g., 'fr-FR' for French or 'es-ES' for Spanish.
  • dddd gives the full day name in the target language.
  • ddd gives the abbreviated day name.
  • This approach works for any .NET-supported culture, so you can easily switch languages for different regional reports.

This makes reports much more user-friendly for international teams or clients.

Notes on Localization

  • DATENAME() doesn’t support localization; it always returns the day name in the SQL Server instance’s default language. If you need multi-language reports, FORMAT() is the way to go.
  • Performance: Using FORMAT() with culture codes is slightly slower than DATENAME(), but for reporting scenarios this overhead is usually negligible.

Which Function Should You Use?

Both DATENAME() and FORMAT() get the job done, but here are a couple of points to keep in mind:

  • Performance: DATENAME() is generally faster since it’s a native SQL function, while FORMAT() relies on .NET formatting under the hood. For large datasets, you might find DATENAME() preferable.
  • Flexibility: FORMAT() offers more control if you need localized or customized date outputs, not just day names.
  • Compatibility: If you’re on SQL Server 2008 or earlier, FORMAT() isn’t available, so you’ll need to stick with DATENAME().

Conclusion

Adding day names to reports is simple with SQL Server. DATENAME() works great for quick English outputs, while FORMAT() gives you both abbreviated names and multilingual support. With a little planning, you can make your reports readable and globally friendly without extra mapping tables or complex logic.