Current Week - 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

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 09:56:37

How can I filter Current Weeek Date? For example today week dates starts on April 27 and ends on May 3. Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 09:59:41

Do you know what books online is?

Also the starting day of the week is configurable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 09:59:44

[code]SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '18991231'),
DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000106')[/code]

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:06:31

Thanks Peso. However, I just want to filter the current week using Where function.Is that possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 10:08:49

[code]SELECT *
FROM Table1
WHERE Col1 >=DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '18991231')
AND Col1 < DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000107')[/code]

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:15:43

That's really helpful. What if I want to get the previous date week range? thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 10:19:33

quote:
Originally posted by cutiebo2t

That's really helpful. What if I want to get the previous date week range? thanks

Understand the solution Peso gave you and modify it to get the date range for previous week.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:20:39

I think I would understand if I get this last one. Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-30 : 10:25:52

maybe this will help you understand and modify week range:


select
dateadd(week, datediff(week, 0, (getdate())), 0) as beginning_of_this_week
,dateadd(week, datediff(week, 0, (getdate())), 6) as ending_of_this_week
,dateadd(week, datediff(week, 0, (getdate())), -7) as beginning_of_previous_week
,dateadd(week, datediff(week, 0, (getdate())), -1) as ending_of_previous_week

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:31:37

I think I got different results. I prefer Pesos's formula. I would like to get 4 week range. Starting current week to the 4th week (backward). Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 10:37:09

quote:
Originally posted by cutiebo2t

I think I got different results. I prefer Pesos's formula. I would like to get 4 week range. Starting current week to the 4th week (backward). Thanks

[ice pick to temple]

Do you know what BOL is?

[/ice pick to temple]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:38:53

Can any one help?

X002548
Not Just a Number

15586 Posts

Posted - 2008-04-30 : 10:44:20

quote:
Originally posted by cutiebo2t

Can any one help?

Can you answer a question?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:47:13

I need to get the next previous 3 week range. Can anyone help? Here's the current week formula:

SELECT *
FROM dbo.TechPhonesCalib
WHERE (SesDate < DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000107')) AND (SesDate >= DATEADD(WEEK, DATEDIFF(WEEK, '19000101',
GETDATE()), '18991231'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 10:51:43

quote:
Originally posted by cutiebo2t

I need to get the next previous 3 week range. Can anyone help? Here's the current week formula:

SELECT *
FROM dbo.TechPhonesCalib
WHERE (SesDate < DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000107')) AND (SesDate >= DATEADD(WEEK, DATEDIFF(WEEK, '19000101',
GETDATE()), '18991231'))

Try atleast once to get your result from solution provided before asking again.

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 10:52:49

I did but I'm getting error. I'm not really sure what numbers are? 18991231 19000107 19000101. Sorry

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 10:57:48

They are not numbers. If you read Books Online as suggested to you, you wil lfind out what they are.

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 11:01:29

Hi Peso. Can you help me with the last 3 weeks. It will be very helpful with my report which is due today. I promise that I'll try to understand everything once this is done then I won't bug you with the same question. I just need an urgent formula for this. Thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-30 : 11:26:09

still don't understand that you...eh, nevermind.


select
dateadd(week, datediff(week, 0, (getdate())), -28) as [bof -3 week]
,dateadd(week, datediff(week, 0, (getdate())), -22) as [eof -3 week]
,dateadd(week, datediff(week, 0, (getdate())), -21) as [bof -2 week]
,dateadd(week, datediff(week, 0, (getdate())), -15) as [eof -2 week]
,dateadd(week, datediff(week, 0, (getdate())), -14) as [bof -2 week]
,dateadd(week, datediff(week, 0, (getdate())), -8) as [eof -2 week]
,dateadd(week, datediff(week, 0, (getdate())), -7) as [bof -1 week]
,dateadd(week, datediff(week, 0, (getdate())), -1) as [eof -1 week]
,dateadd(week, datediff(week, 0, (getdate())), 0) as [bof 0 week]
,dateadd(week, datediff(week, 0, (getdate())), 6) as [eof 0 week]
,dateadd(week, datediff(week, 0, (getdate())), 7) as [bof 1 week]
,dateadd(week, datediff(week, 0, (getdate())), 13) as [eof 1 week]

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-04-30 : 11:29:49

Thanks. However, I would prefer the results in the Where function. Like for this current week:

SELECT *
FROM dbo.TechPhonesCalib
WHERE (SesDate < DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000107')) AND (SesDate >= DATEADD(WEEK, DATEDIFF(WEEK, '19000101',
GETDATE()), '18991231'))

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-04-30 : 11:45:13

[code]
declare @var1 nvarchar(50);
set @var1 = 'why'+' am I so lazy?';

select
@var1 as [Did I ask myself:]
,convert(nvarchar(50), ('Today on: ' + left(getdate(),6))) as [Just date :-)]
,'Is it me, or BOL is down' as [Possible Answer]
where getdate() between DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '18991231')
AND DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000107')
[/code]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources