Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Squery- Start day- Monday Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 4:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 1, 2014 2:14 PM
Points: 100, Visits: 435
Hi All

I have a query to fetch the starting day as "Monday" between any 2 selected dates.

For eg:

Start Date is : 08/12/2012
End Date is 08/19/2012

and I wanted the output as:

Date start of week
08/12/2012 08/06/2012 ----------------that is the Monday
08/13/2012 08/13/2012--------start day Monday
08/14/2012 08/13/2012-Monday
....
08/19/2012 08/13/2012


I use the below query as of now

(select DISTINCT Date_Column,CONVERT(date, DATEADD(wk, DATEDIFF(wk, 0, DATEADD(d, - 1,Date_Column)), 0), 103) AS Week,DATEPART(Week,Date_Column) as Week_Num
from dbo.date_dim
where convert(date,Date_Column,103) BETWEEN
dateadd(wk, datediff(Wk, 0, DATEADD(dd,-1,'08/12/2012')), 0)
AND dateadd(wk, datediff(wk, 0, DATEADD(dd,-1,'08/19/2012')), 0)
)



I know my query returns wrong results...could any 1 please know how to achieve the result.

Thanks
Post #1398902
Posted Thursday, December 20, 2012 4:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, February 1, 2014 2:14 PM
Points: 100, Visits: 435
My where clause should be just between startdate and enddate...but wheras I have use start of week also in the where clause...

I hope wat I had figured out is right..

Thanks
Post #1398906
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse