Member Avatar for Link82

Hi everyone,

So I need to find out if a date entered is a Sunday. If it is, I gotta do something special with this data. I'm not sure if Datepart will help or how to use it effectively to extract if it's a Sunday or if it's 1st day of the week (well if the week starts at Sunday).

Any help? Thanks.

  • 2 Contributors
  • 1 Reply
  • 131 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by sknake

Dani AI

Generated

wanted a simple way to tell whether a date falls on a Sunday. pointed toward the built‑in weekday functions, which is a fine start, but there are a few important caveats worth calling out and a robust alternative for production use.

DATEPART(weekday, ...) returns numeric values that depend on the session setting SET DATEFIRST; DATENAME(weekday, ...) returns a language‑specific name. See the docs for DATEPART, DATENAME and SET DATEFIRST for details. That means a simple numeric test can give different results across sessions or servers unless the session settings are controlled.

A session‑independent, language‑independent numeric test is to use an anchor date known to be a Sunday and take the day difference modulo 7. For example:

-- returns 1 when OrderDate is Sunday, 0 otherwise
CASE WHEN (DATEDIFF(day, '1900-01-07', OrderDate) % 7) = 0 THEN 1 ELSE 0 END

The anchor '1900-01-07' is a Sunday, so the expression equals 0 only for Sundays. This approach is stable regardless of SET DATEFIRST and session language. For large tables, put this into a computed persisted column and index it for fast queries:

ALTER TABLE dbo.Orders
ADD IsSunday AS (CASE WHEN (DATEDIFF(day, '1900-01-07', OrderDate) % 7) = 0 THEN 1 ELSE 0 END) PERSISTED;

CREATE INDEX IX_Orders_IsSunday ON dbo.Orders(IsSunday);

For quick one‑off checks in a controlled session, explicitly setting SET DATEFIRST 7 and using DATEPART is simpler; for reliable, multi‑session logic or indexed tests, the DATEDIFF modulo method is recommended. See DATEDIFF for behavior and performance notes.

Member Avatar for sknake 
Select GetDate() --2009-09-17 16:47:34.720
Select DatePart(dw, GetDate()) --5
Select DateName(dw, GetDate()) --Thursday
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.