date comparison and filtering

Im creating a stored proc where i need to filter an Orders table
results based on a date field as following:
The column i'm filtering is called DeliveryDate
How to I filter DeliveryDate for This W, This Month.
This W:
Do I have to calculate the start of w and end of w and then use
a between #start# and #end#? How is this done.
This Month:
Do I have to calculate the start of month and end of month and then use
a between #start# and #end#
Does SQL provide specialized date functions for this?
Thank YouAre youo sure you mean "filter" A Filter is something that block some
records and lets some records through, based on a predicate.
If you actually want to filter the records, you use a Where Clause
Select * From TableName
Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
To filter for only thos month, you can use the following expressions:
Foor the StartDate:
Convert(VarChar(6), getDate(), 112) + '01'
And for endDate, use:
Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
To filter for the current w, you have to decide what day of the w you
want to use... And set a Server-wide setting called DateFirst. Set it to a
value = 1 for Monday, 2 for Tuesday, etc.
Set DateFirst 1 -- (to make Monday the first day of the w)
And then use these expressions:
StartDate:
convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
getdate()), 112),
and EndDate:
convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
getdate()), 112)
"Opa" wrote:

> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>|||Thanks a lot.
I will try your suggestions.
"CBretana" wrote:
> Are youo sure you mean "filter" A Filter is something that block some
> records and lets some records through, based on a predicate.
> If you actually want to filter the records, you use a Where Clause
> Select * From TableName
> Where DeliveryDate >= <StartDate> And DeliveryDate <EndDate>
> To filter for only thos month, you can use the following expressions:
> Foor the StartDate:
> Convert(VarChar(6), getDate(), 112) + '01'
> And for endDate, use:
> Convert(VarChar(6), DateAdd(month, 1, getDate(), 112) + '01'
>
> To filter for the current w, you have to decide what day of the w yo
u
> want to use... And set a Server-wide setting called DateFirst. Set it to
a
> value = 1 for Monday, 2 for Tuesday, etc.
> Set DateFirst 1 -- (to make Monday the first day of the w)
> And then use these expressions:
> StartDate:
> convert(varChar(8), dateadd(day, 1-datepart(dw, getdate()),
> getdate()), 112),
> and EndDate:
> convert(varChar(8), Dateadd(day, 8-datepart(dw, getdate()),
> getdate()), 112)
> "Opa" wrote:
>|||Another way you can do this
for this month
where Datepart(mm,deliverydate) = datepart(mm,getdate())
for this w
where datepart(wk,deliverydate) = datepart(wk,getdate())
performance may not be the best when you use functions of any type in the
where clause
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Opa" <Opa@.discussions.microsoft.com> wrote in message
news:98449239-878D-41E4-8DC6-57CB5C43AF6D@.microsoft.com...
> Im creating a stored proc where i need to filter an Orders table
> results based on a date field as following:
> The column i'm filtering is called DeliveryDate
> How to I filter DeliveryDate for This W, This Month.
> This W:
> Do I have to calculate the start of w and end of w and then use
> a between #start# and #end#? How is this done.
> This Month:
> Do I have to calculate the start of month and end of month and then use
> a between #start# and #end#
> Does SQL provide specialized date functions for this?
> Thank You
>
>
>
>
>