-1

Imagine I have a SQL Server table as shown below, named dbo.tblNAV:

Ticker   ISIN          Date         Price
------------------------------------------
TSLA     US88160R1014  2021-08-09   45454
TSLA     US88160R1014  2021-08-10   45455
TSLA     US88160R1014  2021-08-11   45456
TSLA     US88160R1014  2021-08-12   45457
TSLA     US88160R1014  2021-08-13   45458
TSLA     US88160R1014  2021-08-16   45459

How can I select the data only for Mondays and Fridays?

I am a beginner in SQL Server and almost have no idea to handle this. I would appreciate your help.

I am using Microsoft SQL Server Management Studio.

Dale K
  • 25,246
  • 15
  • 42
  • 71
K saman
  • 151
  • 6
  • 3
    Please show your research and attempt. – Dale K Sep 05 '21 at 10:19
  • 1
    Does this any your question? [Get day of week in SQL Server 2005/2008](https://stackoverflow.com/questions/1110998/get-day-of-week-in-sql-server-2005-2008) – Thom A Sep 05 '21 at 11:01

2 Answers2

2

You can use something called DATENAME(interval, date) -> more on that here which will return name of a day. Function may be called DAYNAME in some DBs like here

I.E.

SELECT DAYNAME ('2011-05-30') "dayname" FROM DUMMY;

will return "Monday"

Mati
  • 389
  • 3
  • 6
  • 16
2

SQL Server does not make this easy to do across all systems. The problems are:

  • datename() returns the name of the week, but the language depends on local culture settings.
  • datepart() returns the day of week number, but the start of the week depends on local settings.

If you are working in English, then you can use datename():

where datename(weekday, n.date) in ('Monday', 'Friday')

If you need a culture-independent method, then you can use format() with a culture argument:

where format(n.date, 'ddd', 'us-en') in ('Mon', 'Fri')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • For language agnostic, what about `(DATEDIFF(DAY, 0, n.date) % 7) IN (0, 4)`? – MatBailie Sep 05 '21 at 20:36
  • @MatBailie . . . I'm not really a fan of assuming the day-of-the-week of date `0`, but that would also work. – Gordon Linoff Sep 05 '21 at 20:44
  • Then just pick a more recent and known reference point? Such as 2000-01-01? (That's a Saturday. So, `IN (3, 6)`, or just find any Monday to base from?) – MatBailie Sep 05 '21 at 20:48