| Author |
Topic |
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
 Posted - 2009-03-05 : 02:23:23
|
Can any clarify whether any function is there to build the logic:Based on the input date i need to construct a logic for weekend datesFor Example: if i have given input date as '01 jan 2009' the output should be:Week1_st_Dt Week1_end_Dt Week2_end_dt Week3_end_dt week4_end_dt week5_end_dt Month_end_dt 2009-01-01 2009-01-02 2009-01-09 2009-01-16 2009-01-23 2009-01-30 2009-01-31 |
|
|
matty
Posting Yak Master
161 Posts |
Posted - 2009-03-05 : 03:58:53
|
| DECLARE @date DATETIME,@month intSET @date = '2009-01-01'SET @month = DATEPART(m,@date)SELECT @date AS Week1_st_Dt, DATEADD(d,6 - DATEPART(dw,@date),@date) AS Week1_end_Dt, DATEADD(d,13 - DATEPART(dw,@date),@date) AS Week2_end_dt, DATEADD(d,20 - DATEPART(dw,@date),@date) AS Week3_end_dt, DATEADD(d,27 - DATEPART(dw,@date),@date) AS week4_end_dt, CASE WHEN DATEPART(m,DATEADD(d,34 - DATEPART(dw,@date),@date)) = @month THEN DATEADD(d,34 - DATEPART(dw,@date),@date) ELSE NULL END AS week5_end_dt, DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS Month_end_dt |
 |
|
|
sent_sara
Constraint Violating Yak Guru
377 Posts |
Posted - 2009-03-05 : 05:10:00
|
[code]Txs Matty[/code]quote:
Originally posted by matty
DECLARE @date DATETIME,@month intSET @date = '2009-01-01'SET @month = DATEPART(m,@date)SELECT @date AS Week1_st_Dt, DATEADD(d,6 - DATEPART(dw,@date),@date) AS Week1_end_Dt, DATEADD(d,13 - DATEPART(dw,@date),@date) AS Week2_end_dt, DATEADD(d,20 - DATEPART(dw,@date),@date) AS Week3_end_dt, DATEADD(d,27 - DATEPART(dw,@date),@date) AS week4_end_dt, CASE WHEN DATEPART(m,DATEADD(d,34 - DATEPART(dw,@date),@date)) = @month THEN DATEADD(d,34 - DATEPART(dw,@date),@date) ELSE NULL END AS week5_end_dt, DATEADD(dd, -DAY(DATEADD(m,1,@date)), DATEADD(m,1,@date)) AS Month_end_dt
|
|
|
|
|
|
|
Week End Date logic