Current Week Date Range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Current Week Date Range

Author

Topic

srucker
Starting Member

26 Posts

Posted - 2009-05-21 : 16:38:05

I am trying to get the current weeks date range.

ex:
Today is 5/21/2009
I would like the value to return:
5/17/2009 - 5/23/2009

Thanks in advance!!!!!

Qualis
Posting Yak Master

145 Posts

Posted - 2009-05-21 : 17:23:54

Select
Convert(varchar, DateAdd(dd, -(DatePart(dw, GetDate()) - 1), GetDate()), 101) As WeekStart,
Convert(varchar, DateAdd(dd, (7 - DatePart(dw, GetDate())), GetDate()), 101) As WeekEnd

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-21 : 17:57:21

[code]
select
StartofWeek = dateadd(dd,(datediff(dd,-53684,getdate())/7)*7,-53684),
EndofWeek = dateadd(dd,((datediff(dd,-53684,getdate())/7)*7)+6,-53684)

Results:

StartofWeek EndofWeek
----------------------- -----------------------
2009-05-17 00:00:00.000 2009-05-23 00:00:00.000
[/code]

More info about using SQL Server datetime here:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

srucker
Starting Member

26 Posts

Posted - 2009-05-21 : 18:08:33

I am trying to do this in the expression editor of the Report Designer. The syntax is failing for me and I cannot seem to convert. Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:34:13

start of week
=dateadd("ww",datediff("ww",#01/01/1900#,Now()),#01/01/1900#)
end of week
=dateadd("ww",datediff("ww",#01/01/1900#,Now())+1,#01/01/1900#),

srucker
Starting Member

26 Posts

Posted - 2009-05-26 : 16:26:46

What if we wanted the week range to be from Sun - Sat?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 16:34:02

Change the last value #01/01/1900# accordingly.

E 12°55'05.63"
N 56°04'39.26"

srucker
Starting Member

26 Posts

Posted - 2009-07-29 : 17:09:50

I was able to write the following in sql:

DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)-1

Can anyone help me convert this to an expression for Report Builder in Visual Studio?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 13:54:24

quote:
Originally posted by srucker

I was able to write the following in sql:

DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)-1

Can anyone help me convert this to an expression for Report Builder in Visual Studio?

you can use dateadd and datediff on visual studio also. use now() instead of getdate()

srucker
Starting Member

26 Posts

Posted - 2009-07-30 : 13:59:24

Visual Studio is not recognizing the wk identifier. May I ask what the comperable value may be?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:01:34

its ww

srucker
Starting Member

26 Posts

Posted - 2009-07-30 : 14:10:53

So here it the output of our discussion
DATEADD(ww,DATEDIFF(ww,0,GETDATE())-1,0)-1
VS is claiming that wk and/or ww is an unrecognized identifier.

Everything looks correct to me so I am at a loss w/ VS?

thx in adv!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-30 : 14:13:12

=DATEADD(ww,DATEDIFF(ww,#01/01/1900# ,Now())-1,#01/01/1900# )-1

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2012-07-02 : 23:13:24

how can i get weekly date of data?
i would like to schedule every monday.

for example 2nd july 2012 to 8th july 2012 of data,
then next monday send 9 to 15 july data and so on..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-03 : 02:03:17

dateadd(dd,(datediff(dd,0,getdate())/7)*7,0)

and

dateadd(dd,((datediff(dd,0,getdate())/7)*7)+7,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources