469,128 Members | 1,661 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,128 developers. It's quick & easy.

Last Day Of Previous Month...with a twist

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

May 10 '07 #1
9 4364
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
May 10 '07 #2
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
May 10 '07 #3
On 10 May, 13:03, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
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

<brymcgu...@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- Hide quoted text -

- Show quoted text -

Hi Dan & Plamen,

Thanks for the solutions. Both worked great.

B
May 10 '07 #4
"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).
May 10 '07 #5
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
May 10 '07 #6
"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
May 10 '07 #7
>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.

May 10 '07 #8
On 10 May, 23:22, --CELKO-- <jcelko...@earthlink.netwrote:
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

May 11 '07 #9
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
May 11 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Killer | last post: by
5 posts views Thread by Ray via SQLMonster.com | last post: by
10 posts views Thread by ashore | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.