Start Date and End date in SQL

Question

Hi,

Want to create a report on a weekly basis so will pass one start date parameter (and adding 7 days considered as end date) Now issue is if • Select Monday as start date, it takes 7 days from start date so end date is Sunday’s date • Select Tuesday, it takes coming Monday also.

We do not want this way if I select any day during the week it should give me end date as Sunday of that week Please let me know how to do it. Regards, Vandana

Answer 1

0

SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS Today,            
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 6) AS Sunday            
    DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 7) AS NextMonday            
            
SELECT  *            
FROM    Table1            
WHERE   Col1 >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)            
    AND Col1 < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 7)

answered Oct 21 '09 at 05:44 AM

Peso
1.6k ● 4 ● 6 ● 8

(comments are locked)

Answer 2

0

I think OP wants to pass the starting date.

declare @date datetime            
set @date='2009-10-21'            
            
SELECT  *            
FROM    Table1            
WHERE   Col1 >= @date            
    AND Col1 < DATEADD(DAY, DATEDIFF(DAY, 0, @date) / 7 * 7, 7)

answered Oct 21 '09 at 05:56 AM

Madhivanan
1.1k ● 1 ● 2 ● 6

(comments are locked)

Answer 3

hi, Thanks for your help it has really worked. now my report is generating fine.

also would like to know if dates are in different months, how can i restrict the data to show in report eg my start date is 28 Sept 09 so this code will consider 4 Oct 09 as End Date howerver they are in differnt month, want to show only for 3 days i.e from 28 sep -30 sept rest it should show 0 how can i show data with 0 value when two dates are in differnt month.

please help to reslove this. Regards Vandana

Start Date and End date in SQL

Follow this question

Related Questions