Query to list the Weekdays

Question

Post reply

Query to list the Weekdays

tickler_foryou

SSC Veteran

Points: 211
More actions
March 31, 2008 at 9:13 pm

#379519

Hi friends,
I have a Order_date column where order dates are captured in SQL server 2005 DB, I need to find the order dates that falls on Week Days only for the last monthand show my report.
Select order_date ,product from Orders
where order_date between 03-01-2008 and 03-01-2008
and order_date ......(should be only Week Days)
Please help me in this Query,Thanks in Adv..!!!!
Claoker

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

This link should help you.

http://ryanfarley.com/blog/archive/2005/02/14/1685.aspx

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

tickler_foryou (3/31/2008)
Hi friends,
I have a Order_date column where order dates are captured in SQL server 2005 DB, I need to find the order dates that falls on Week Days only for the last monthand show my report.
Select order_date ,product from Orders
where order_date between 03-01-2008 and 03-01-2008
and order_date ......(should be only Week Days)
Please help me in this Query,Thanks in Adv..!!!!
Claoker

SET DATEFIRST 1 --Set first day of week = Monday

SELECT DATENAME(dw,SomeDate),*

FROM dbo.Orders

WHERE Order_Date >= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-1,0) --First of previous month

AND Order_Date < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) --First of current month

AND DATEPART(dw,Order_Date) < 6

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

p.s. A join to a Calendar table would be even more effective...

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Jeff Moden (3/31/2008)
p.s. A join to a Calendar table would be even more effective...
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Jeff's link is excellent for your solution. You can also go thru this links:

http://codeinet.blogspot.com/2006/08/auxiliary-calendar-table-for-sql.html

http://codeinet.blogspot.com/2006/09/audit-table-data-changes-in-sql-server.html

🙂