Last Day Of Previous Month...with a twist

[brymcguire]

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

[Dan Guzman]

The query needs to identify all sales between the last day of the

previous month and going back one year.

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

<br********@googlemail.comwrote in message
news:11*********************@n59g2000hsh.googlegro ups.com...

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

[Plamen Ratchev]

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

[brymcguire]

On 10 May, 13:03, "Dan Guzman" <[e-mail]>
wrote:

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

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

<[e-mail] in message

news:11*********************@n59g2000hsh.googlegro ups.com...

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- Hide quoted text -

- Show quoted text -

Hi Dan & Plamen,

Thanks for the solutions. Both worked great.

B

[Chris.Cheney]

"Plamen Ratchev" <Pl****@SQLStudio.comwrote in news:BjE0i.8597$Ut6.2872
@newsread1.news.pas.earthlink.net:

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

Is it rash to assume that CURRENT_TIMESTAMP will not be called twice for
each row? Perhaps only twice for the whole query? If it is called more than
once (as implied by the query), there will be a problem if the query is run
at a time such that midnight on the last day of the month occurs between
two calls. Or have I missed something (which I admit is likely).

[Plamen Ratchev]

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

[Chris.Cheney]

"Plamen Ratchev" <Pl****@SQLStudio.comwrote in news:STG0i.9686$j63.2095
@newsread2.news.pas.earthlink.net:

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

I hoped that was the case - thanks for confirming it.

Chris

[--CELKO--]

>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. <<

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.

[brymcguire]

On 10 May, 23:22, --CELKO-- <[e-mail]:

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. <<

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.

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

[Hugo Kornelis]

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

>>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. <<

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.

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