How to get Week start date based on user input date

Question

0.00/5 (No votes)

See more:

Dear sir,

How to get week start date in SQL queries, if i am entering any date.

suppose i select 5-Dec-13, i want to display week start date(30-Nov-13) based on input date. means,i want saturday as week start date(30-Nov-13) to end date friday(5-Dec-13).

only i want to dispaly week start date based on user input date in SQL Queries.

Posted 6-Dec-13 23:53pm

kamalsekhar

Add a Solution

Comments

Zoltán Zörgő 13-Dec-13 11:39am

Any progress?

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-12-07T02:28:00

The sample you had given is quite unclear, but have a look here:

SQL

declare @d datetime
set @d='2013-12-05'

select 
DATEADD(dd, -(DATEPART(dw, @d)-1), @d) [WeekStart], 
DATEADD(dd, 7-(DATEPART(dw, @d)), @d) [WeekEnd]

It will return 2013-12-01 (Sunday) as week start, and 2013-12-07 (Saturday) as week end. If you want custom week start, play with the constants.

[Update]
Here is the modified version that makes a week to start on Saturday and end on Friday:

SQL

select 
DATEADD(dd, -(DATEPART(dw, @d)), @d) [WeekStart], 
DATEADD(dd, 6-(DATEPART(dw, @d)), @d) [WeekEnd]

Mike Meinz · Answer 2 · 2013-12-07T02:28:00

To get the date of the day that is the start of the week, here are three examples:

Replace GetDate() with your date column name or date value.

SQL

Select DateAdd(wk, DateDiff(wk, 0, GetDate()) - 1, 0) as LastWeekStartDate
Select DateAdd(wk, DateDiff(wk, 0, GetDate()), 0) as ThisWeekStartDate
Select DateAdd(wk, DateDiff(wk, 0, GetDate()) + 1, 0) as NextWeekStartDate

Note: The result will depend on the SQL Server's @@DATEFIRST parameter.

Mahesh Bailwal · Answer 3 · 2013-12-07T00:24:00

Solution 1

The DATEADD function simply allows you to add or subtract the specified number of units of time to a specified date/time value.

The format of the DATEADD function is as follows:

DATEADD(<Unit of time>, <Units>, <Date>)

-- to add 5 days to September 1, 2011 the function would be
DATEADD(DAY, 5, '9/1/2011')

-- to subtract 5 months from September 1, 2011 the function would be
DATEADD(MONTH, -5, '9/1/2011')

select DATEADD(DAY,-5, '12/5/2013') as WeakStartDate

Posted 7-Dec-13 0:24am

Mahesh Bailwal

Comments

Zoltán Zörgő 7-Dec-13 8:15am

Won't be helpful on it's own :(

Mahesh Bailwal 7-Dec-13 8:27am

Can you please tell me what was wrong with this solution.

Zoltán Zörgő 7-Dec-13 8:31am

It is working only for some of the inputs. You substract a constant! See my post.

Mahesh Bailwal 7-Dec-13 8:47am

Sorry, my intention was to give just any idea about "DATEADD" in SQL not the exact answer. :(

Zoltán Zörgő 7-Dec-13 8:49am

Than it is not an answer - OP won't be able to solve the problem just with that. The missing part is the trick. You could have posted this as a comment.

[no name] 7-Dec-13 8:53am

Also you did not give the complete answer! "...start playing" I'm sure you would not take the risk to be +- 1 day wrong...

Zoltán Zörgő 7-Dec-13 15:57pm

To me Saturday as week starting day makes no sense. "Start playing" does not mean, that the answer is incomplete - but there you have the update, if you can't figure out by yourself.

idle63 · Answer 4 · 2013-12-07T00:54:00

Solution 2

You can use DateTime.DayOfWeek to come to the beginning of the week. DayOfWeek starts with 0 for Sunday, so yuo have to take your special beginning of the week into acount.

Something like this:

C#

DateTime dt = DateTime.Now;
dt.AddDays(-((int)dt.DayOfWeek + 1));

Posted 7-Dec-13 0:54am

idle63

Updated 7-Dec-13 1:50am

v2

Comments

Zoltán Zörgő 7-Dec-13 8:15am

Read topic tags: OP wants result in T-SQL!

[no name] 7-Dec-13 8:20am

Uups, you are right, sorry...But maybe it gives OP an idea.
This is called also interdisciplinary... at least I see for you it gave the input to answer it :)

How to get Week start date based on user input date

4 solutions

Solution 4

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0