Current month syntax

A little help please...
I need to extract records in which the FileRecDate falls within the current
month/current year range. Also may need to filter by previous month/current
year; current year and prior year.
Any ideas?
Thanks,
Kenselect getdate()
Also:
select datepart(mm,getdate())
select datepart(dd,getdate())
select datepart(yy,getdate())
It is possible to get more fancy. You could declare a "start" and an "end"
variable and set the values according to your business logic. The variables
would then be used to filter your data within your query/stored procedure.
Keith
"Ken D." <KenD@.discussions.microsoft.com> wrote in message
news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>A little help please...
> I need to extract records in which the FileRecDate falls within the
> current
> month/current year range. Also may need to filter by previous
> month/current
> year; current year and prior year.
> Any ideas?
> Thanks,
> Ken|||Keith,
Actually, I tried this logic and it worked.
WHERE Month(FileRecDate) = Month(GetDate())AND
Year(FileRecDate)=Year(GetDate())
Thank you so much for your input...
Ken
"Keith Kratochvil" wrote:

> select getdate()
> Also:
> select datepart(mm,getdate())
> select datepart(dd,getdate())
> select datepart(yy,getdate())
> It is possible to get more fancy. You could declare a "start" and an "end
"
> variable and set the values according to your business logic. The variabl
es
> would then be used to filter your data within your query/stored procedure.
> --
> Keith
>
> "Ken D." <KenD@.discussions.microsoft.com> wrote in message
> news:49A7FD76-49BF-41D8-8437-9BD015FEF288@.microsoft.com...
>
>|||> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
Ugh, how was performance? You might not notice while the table is small,
but wait until you have a reasnable amount of data. If you have an index on
FileRecDate (which you should), you might try something along these lines:
DECLARE @.sd SMALLDATETIME
SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
SELECT ...
WHERE FileRecDate >= @.sd
AND FileRecDate < DATEADD(MONTH, 1, @.sd)
A|||Ken D. wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
VERY BAD and here is why:
You force SQL server to calculate Month(FileRecDate) and
Year(FileRecDate) for each row in the table and prevent
it from using an index that you may have on FileRecDate.
It is much better to write something like this:
WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
Both @.MonthStartDateTime and @.MonthEndDateTime can be either
calculated by the SQL server (you can use CONVERT function)
or passed as parameters.
NEVER, NEVER force the calculation on a field in the WHERE
clause unless it is absolutely necessary.
Yura
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||Ken,
Do not manipulate the column and try to use the pattern:
column <operator> constant
so sql server can considers the expression as a search argument.
Transact-SQL Query
SQL Server Performance Tuning Tips
http://www.sql-server-performance.com/transact_sql.asp
...
where
FileRecDate >= convert(varchar(6), getdate(), 112) + '01'
and FileRecDate < dateadd(month, 1, convert(varchar(6), getdate(), 112) +
'01')
Example:
set showplan_text on
go
select
*
from
northwind.dbo.orders
where
orderdate >= convert(varchar(6), getdate(), 112) + '01'
and orderdate < dateadd(month, 1, convert(varchar(6), getdate(), 112) + '01'
)
go
select
*
from
northwind.dbo.orders
where
year(orderdate) = year(getdate())
and month(orderdate) = month(getdate())
go
set showplan_text off
go
See that in the first execution plan, sql server will performs an index s
in index [OrderDate], but in the second, it will scan index [pk_Orders].
AMB
"Ken D." wrote:
> Keith,
> Actually, I tried this logic and it worked.
>
> WHERE Month(FileRecDate) = Month(GetDate())AND
> Year(FileRecDate)=Year(GetDate())
> Thank you so much for your input...
> Ken
> "Keith Kratochvil" wrote:
>|||> WHERE FileRecDate BETWEEN @.MonthStartDateTime and @.MonthEndDateTime
BETWEEN can be bad too; not necessarily for performance, but certainly when
constructing the end of the range, particularly if the datatype is datetime,
and/or is converted between datetime and smalldatetime, and/or the values in
the table can fall on midnight of the end of the range OR only contain
dates.
http://www.aspfaq.com/2280
A|||Aaron,
I dropped the code in and it works great. Could you explain the syntax for
previous month or current year.
Ken
"Aaron Bertrand [SQL Server MVP]" wrote:

> Ugh, how was performance? You might not notice while the table is small,
> but wait until you have a reasnable amount of data. If you have an index
on
> FileRecDate (which you should), you might try something along these lines:
> DECLARE @.sd SMALLDATETIME
> SET @.sd = DATEDIFF(DAY,DAY(GETDATE())-1,GETDATE())
> SELECT ...
> WHERE FileRecDate >= @.sd
> AND FileRecDate < DATEADD(MONTH, 1, @.sd)
> A
>
>|||Aaron Bertrand [SQL Server MVP] wrote:
>
> BETWEEN can be bad too; not necessarily for performance, but certainly whe
n
> constructing the end of the range, particularly if the datatype is datetim
e,
> and/or is converted between datetime and smalldatetime, and/or the values
in
> the table can fall on midnight of the end of the range OR only contain
> dates.
I simply assumed that Ken knows these caveats.
I usually define
@.StartDateTime and @.EndDateTime
as
'<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
11:59:59.9999 pm'
Or it can be as simple as
@.EndDateTime = DATEADD(mm, 1, @.StartDateTime )
and
WHERE (FileRecDate >= @.StartDateTime AND FileRecDate < @.EndDateTime)|||> I simply assumed that Ken knows these caveats.
I try not to make assumptions here. I would rather overload with
information than miss something and his app breaks.

> '<month>/01/<year>' (i.e. 12 am) and '<month>/<end day>/<year>
> 11:59:59.9999 pm'
I see two problems here.
(1) If you have dateformat d/m/y or English regional settings or French
language, some of these might break (depending on the month). If you're
going to use strings (which you don't have to do), you should at least
strive for ISO standard dates that are unambiguous and cannot be by
people or software (e.g. YYYYMMDD or YYYY-MM-DDTHH:MM:SS[.ms]).
(2) If you have smalldatetime, guess what happens? Your 23:59.9999 gets
rounded up to the next day. That might be bad, depending on the nature of
the data (e.g. you might erroneously include data from the next day, but
timestamped at midnight). In many systems, time is irrelevant (and often
stripped), so this is a common pitfall to watch our for.
I strongly recommend the following topics:
http://www.aspfaq.com/2023
http://www.karaszi.com/SQLServer/info_datetime.asp