|
Home > Archive > MS SQL Server > March 2005 > SQL question? Find record in this "week"?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL question? Find record in this "week"?
|
|
| D. Shane Fowlkes 2005-03-30, 9:40 am |
| OK - this should be simple enough. How can I find records where a date
field has a date stored that is within "this week"?
Pseudo logic:
SELECT Field1, Field2 FROm Table WHERE EventDate IS within this week...
Thanks!
| |
| Francesco Anti 2005-03-30, 9:40 am |
| SELECT Field1, Field2 FROm Table WHERE
DATEPART(wk,EventDat
e)= DATEPART(wk,GetDate(
))
Francesco Anti
"D. Shane Fowlkes" <shanefowlkes@h-o-t-m-a-i-l.com> wrote in message
news:OQYI$qTNFHA.2356@TK2MSFTNGP14.phx.gbl...
> OK - this should be simple enough. How can I find records where a date
> field has a date stored that is within "this week"?
>
> Pseudo logic:
>
> SELECT Field1, Field2 FROm Table WHERE EventDate IS within this week...
>
> Thanks!
>
>
>
>
>
| |
| Aaron [SQL Server MVP] 2005-03-30, 9:40 am |
| Assuming "normal" DATEFIRST settings (first day of week is Sunday):
DECLARE @dt SMALLDATETIME
SET @dt = DATEADD(DAY, 1-DATEPART(DW, GETDATE()), DATEDIFF(DAY, 0,
GETDATE()))
SELECT COLUMNS FROM table
WHERE EventDate >= @dt
AND EventDate < (@dt + 7)
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"D. Shane Fowlkes" <shanefowlkes@h-o-t-m-a-i-l.com> wrote in message
news:OQYI$qTNFHA.2356@TK2MSFTNGP14.phx.gbl...
> OK - this should be simple enough. How can I find records where a date
> field has a date stored that is within "this week"?
>
> Pseudo logic:
>
> SELECT Field1, Field2 FROm Table WHERE EventDate IS within this week...
>
> Thanks!
>
>
>
>
>
| |
| D. Shane Fowlkes 2005-03-30, 9:40 am |
| Thanks guys! I'll give it a try.
--
"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:e2UxW2TNFHA.3704@TK2MSFTNGP12.phx.gbl...
> Assuming "normal" DATEFIRST settings (first day of week is Sunday):
>
> DECLARE @dt SMALLDATETIME
> SET @dt = DATEADD(DAY, 1-DATEPART(DW, GETDATE()), DATEDIFF(DAY, 0,
> GETDATE()))
>
> SELECT COLUMNS FROM table
> WHERE EventDate >= @dt
> AND EventDate < (@dt + 7)
>
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>
> "D. Shane Fowlkes" <shanefowlkes@h-o-t-m-a-i-l.com> wrote in message
> news:OQYI$qTNFHA.2356@TK2MSFTNGP14.phx.gbl...
>
>
| |
| Aaron [SQL Server MVP] 2005-03-30, 9:40 am |
| FYI, this will effectively eliminate the use of any index on EventDate...
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Francesco Anti" <fanti_@_sicosbt.it> wrote in message
news:e5Nsk1TNFHA.2356@TK2MSFTNGP14.phx.gbl...
> SELECT Field1, Field2 FROm Table WHERE
> DATEPART(wk,EventDat
e)= DATEPART(wk,GetDate(
))
>
> Francesco Anti
>
> "D. Shane Fowlkes" <shanefowlkes@h-o-t-m-a-i-l.com> wrote in message
> news:OQYI$qTNFHA.2356@TK2MSFTNGP14.phx.gbl...
>
>
| |
|
| SELECT Field1, Field2 FROm Table
WHERE DATEPART(wk, EventDate) = DATEPART(wk, GETDATE()) -- this week
AND DATEPART(yy, EventDate) = DATEPART(yy, GETDATE()) -- this year
"D. Shane Fowlkes" wrote:
> OK - this should be simple enough. How can I find records where a date
> field has a date stored that is within "this week"?
>
> Pseudo logic:
>
> SELECT Field1, Field2 FROm Table WHERE EventDate IS within this week...
>
> Thanks!
>
>
>
>
>
>
| |
| Alejandro Mesa 2005-03-30, 9:40 am |
| Try,
SELECT Field1, Field2
FROm Table
WHERE datediff(week, EventDate, getdate()) = 0
SQL Server will not use an index seek if the table has an index by EventDate
because the expression used in the WHERE clause is not considered a search
argument.
AMB
"D. Shane Fowlkes" wrote:
> OK - this should be simple enough. How can I find records where a date
> field has a date stored that is within "this week"?
>
> Pseudo logic:
>
> SELECT Field1, Field2 FROm Table WHERE EventDate IS within this week...
>
> Thanks!
>
>
>
>
>
>
| |
| Aaron [SQL Server MVP] 2005-03-30, 9:40 am |
| Oh, and this also assumes that all data in the table is in the same year!
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|
|
|
|
|