Hi,



I have a requirement to design a query that identifies items sold
between two dates. There is a 'SoldDate' datetime field used to
register what date the item was sold.

The query needs to identify all sales between the last day of the
previous month and going back one year.

What I would like to do is to design a query / stored procedure that
will dynamically create the criteria to allow the client to simply run
the query or stored proc.

I know how to establish the last day of the previous month part, I'm
just not sure of how best to design the remainder of the query.

Thank in advance

Below is one method. I generally recommend using >= and < instead of
BETWEEN for datetime data types. This will better handle datetime values
that include time.

WHERE
SoldDate >= CAST(CONVERT(CHAR(6), DATEADD(year, -1, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)
AND
SoldDate < CAST(CONVERT(CHAR(6), DATEADD(day, 0, DATEDIFF(day, 0,
GETDATE())), 112) + '01' AS datetime)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<brymcguire (AT) googlemail (DOT) com> wrote

Here is an alternative to Dan's method, just using only the datetime
functions:

WHERE
SoldDate >= DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0,
CURRENT_TIMESTAMP), 0))
AND
SoldDate < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On 10 May, 13:03, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote:

Hi Dan & Plamen,

Thanks for the solutions. Both worked great.

B

Although CURRENT_TIMESTAMP/GETDATE is listed as non-deterministic, it is
deterministic at the statement level (it is evaluated only once per
statement).

Try this query on any large table:

SELECT *, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
FROM AnyLargeTable
WHERE CURRENT_TIMESTAMP = CURRENT_TIMESTAMP
AND CURRENT_TIMESTAMP = CURRENT_TIMESTAMP


Plamen Ratchev
http://www.SQLStudio.com

Instead of using procedural coding, why not use a table of the
reporting periods for a decade or two? A simple BETWEEN predicate
will classify each sale quickly and give you extra control over non-
operating days, etc.

On 10 May, 23:22, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Hi Joe,

Thanks for the feedback. Your solution of "using a table of reporting
periods" was one of a number that I did think about at the time.
However, as I have learned over the years when it comes to using SQL,
there is , as we somtimes say here in the UK, "more than one way to
skin a cat..."

Now I'm not saying that I won't adopt your solution, just that its
good for me to have a number of "options" up my sleeve.

By the way I have read and own a number of your books. They have been
a big help

Thanks

Bryan

On 10 May 2007 15:22:08 -0700, --CELKO-- wrote:

Hi Joe,

When has using a call to a standard function in a query started to be
"procedural coding"?

Using a table of periods is a great technique, especially when there are
exceptions. But in situations without exceptions, using builtin
functions is far faster than joining to a secondary table.

For the original question in this thread, the best and fastest technique
is:

WHERE solddate >= DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'19990101')
AND solddate < DATEADD(month,
DATEDIFF(month,'20000101',CURRENT_TIMESTAMP),
'20000101')

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis