sunday is my week ending day
in [Programming SQL]
|
From: rodchar on 1 Jun 2010 09:27 Hi All, When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect should be 0 because I consider Sunday my week ending day. The answer I get is 1 which is incorrect in my situation. How would I adjust for this? Thanks, rodchar
From: Maria on 1 Jun 2010 10:01 You can use SET DATEFIRST to change first day of the week and check it's current value using SELECT @@DATEFIRST. But I think algorithm if DATEDIFF does not take it into account. Instead you can count number of days and divide it by 7. "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message news:B0DC77EB-7C88-48A1-93C8-E36B1926B41E(a)microsoft.com... > Hi All, > > When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect > should > be 0 because I consider Sunday my week ending day. The answer I get is 1 > which is incorrect in my situation. > > How would I adjust for this? > > Thanks, > rodchar
From: Peso on 1 Jun 2010 11:06 Use integer division SELECT DATEDIFF(DAY, 0, '6/6/2010') / 7 - DATEDIFF(DAY, 0, '6/1/2010') / 7 //Peso "rodchar" <rodchar(a)discussions.microsoft.com> wrote in message news:B0DC77EB-7C88-48A1-93C8-E36B1926B41E(a)microsoft.com... > Hi All, > > When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect > should > be 0 because I consider Sunday my week ending day. The answer I get is 1 > which is incorrect in my situation. > > How would I adjust for this? > > Thanks, > rodchar
From: rodchar on 1 Jun 2010 13:26 thanks all for the help, rod. "rodchar" wrote: > Hi All, > > When I use DATEDIFF(week, '6/1/2010', '6/6/2010') the answer I expect should > be 0 because I consider Sunday my week ending day. The answer I get is 1 > which is incorrect in my situation. > > How would I adjust for this? > > Thanks, > rodchar
|
Pages: 1 Prev: Forming a date Next: IDENTITY INSERT issue with INSERT INTO..SELECT |