Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 datediff

Author  Topic 

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-11-11 : 11:50:16
when you do a date diff this is the result


dt Day Week
11/11/2014 00:00 0 0
10/11/2014 00:00 1 0
09/11/2014 00:00 2 0
08/11/2014 00:00 3 1
07/11/2014 00:00 4 1
06/11/2014 00:00 5 1
05/11/2014 00:00 6 1
04/11/2014 00:00 7 1
03/11/2014 00:00 8 1
02/11/2014 00:00 9 1
01/11/2014 00:00 10 2
31/10/2014 00:00 11 2
30/10/2014 00:00 12 2
29/10/2014 00:00 13 2
28/10/2014 00:00 14 2
27/10/2014 00:00 15 2
26/10/2014 00:00 16 2
25/10/2014 00:00 17 3
24/10/2014 00:00 18 3
23/10/2014 00:00 19 3
22/10/2014 00:00 20 3
21/10/2014 00:00 21 3
20/10/2014 00:00 22 3
19/10/2014 00:00 23 3
18/10/2014 00:00 24 4
17/10/2014 00:00 25 4
16/10/2014 00:00 26 4
15/10/2014 00:00 27 4
14/10/2014 00:00 28 4
13/10/2014 00:00 29 4
12/10/2014 00:00 30 4
11/10/2014 00:00 31 5
10/10/2014 00:00 32 5
09/10/2014 00:00 33 5
08/10/2014 00:00 34 5
07/10/2014 00:00 35 5
06/10/2014 00:00 36 5
05/10/2014 00:00 37 5
04/10/2014 00:00 38 6
03/10/2014 00:00 39 6
02/10/2014 00:00 40 6
01/10/2014 00:00 41 6
30/09/2014 00:00 42 6
29/09/2014 00:00 43 6
28/09/2014 00:00 44 6


expected result


dt Day Weeks
11/11/2014 00:00 0 0
10/11/2014 00:00 1 0
09/11/2014 00:00 2 0
08/11/2014 00:00 3 0
07/11/2014 00:00 4 0
06/11/2014 00:00 5 0
05/11/2014 00:00 6 0
04/11/2014 00:00 7 1
03/11/2014 00:00 8 1
02/11/2014 00:00 9 1
01/11/2014 00:00 10 1
31/10/2014 00:00 11 1
30/10/2014 00:00 12 1
29/10/2014 00:00 13 1
28/10/2014 00:00 14 2
27/10/2014 00:00 15 2
26/10/2014 00:00 16 2
25/10/2014 00:00 17 2
24/10/2014 00:00 18 2
23/10/2014 00:00 19 2
22/10/2014 00:00 20 2
21/10/2014 00:00 21 3
20/10/2014 00:00 22 3
19/10/2014 00:00 23 3
18/10/2014 00:00 24 3
17/10/2014 00:00 25 3
16/10/2014 00:00 26 3
15/10/2014 00:00 27 3
14/10/2014 00:00 28 4
13/10/2014 00:00 29 4
12/10/2014 00:00 30 4
11/10/2014 00:00 31 4
10/10/2014 00:00 32 4
09/10/2014 00:00 33 4
08/10/2014 00:00 34 4
07/10/2014 00:00 35 5
06/10/2014 00:00 36 5
05/10/2014 00:00 37 5
04/10/2014 00:00 38 5
03/10/2014 00:00 39 5
02/10/2014 00:00 40 5
01/10/2014 00:00 41 5
30/09/2014 00:00 42 6
29/09/2014 00:00 43 6
28/09/2014 00:00 44 6
27/09/2014 00:00 45 6
26/09/2014 00:00 46 6
25/09/2014 00:00 47 6
24/09/2014 00:00 48 6


is there a fuction which can help me?

Thanks is advance

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-11-11 : 11:52:49
the bulit in SQL calculation works 9days as the last day for the 1st week but i would like 13days to be the last day of 1st week. Any idea?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-12 : 01:41:36
what is your definition of "weeks" ?

starts on Sunday ? Monday ? or every 7 days from today ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-11-12 : 06:18:00


0-6 days week0
7-13 days week1
14-20 days week2
21-27 days week3
28-34 days week4
35-41 days week5
42-48 days week6

Thank you
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-12 : 06:42:13
dateadiff(day, [date], getdate()) / 7 as week_no


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Muj9
Yak Posting Veteran

75 Posts

Posted - 2014-11-12 : 08:09:58
Thank you
Go to Top of Page
   

- Advertisement -