x

Start Date and End date in SQL

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

more ▼

asked Oct 21, 2009 at 05:24 AM in Default

Vandana Mestri gravatar image

Vandana Mestri
22 2 2 2

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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)
more ▼

answered Oct 21, 2009 at 05:44 AM

Peso gravatar image

Peso
1.6k 5 6 8

(comments are locked)
10|1200 characters needed characters left

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)
more ▼

answered Oct 21, 2009 at 05:56 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 03, 2009 at 08:07 AM

Vandana Mestri gravatar image

Vandana Mestri
22 2 2 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x94

asked: Oct 21, 2009 at 05:24 AM

Seen: 3644 times

Last Updated: Oct 21, 2009 at 05:24 AM