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

avatar image

Vandana Mestri
22 2 2 4

(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

avatar image

Peso
1.6k 5 6 9

(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

avatar image

Madhivanan
1.1k 2 5 9

(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

avatar image

Vandana Mestri
22 2 2 4

(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.

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:

x80

asked: Oct 21, 2009 at 05:24 AM

Seen: 3961 times

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

Copyright 2016 Redgate Software. Privacy Policy