Skip to content
Advertisement

How to return data for specific day names from date column

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.

Advertisement

Answer

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')
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement