Last Date of previous Month

ive been trying very unsucessfully to try and get the last date of the
previous month,
ie if i run the query today i want the result to be 31/01/2006
declare @.d datetime
set @.d='20060201'
select DATEADD(day,-DATEPART(day,@.d),@.d)|||print dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:168DF5B1-BCE5-4601-802A-8FF10CAD3F2E@.microsoft.com...
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006
>|||Have you looked up how to use a Calendar table? The table could
include a code for last (business?) day of each month, so you can pull
out a set of such dates for reports. I
Your other options are computational. There is the "COBOL method" and
the "UNIX method". The Cobol guys think of dates as character strngs,
and they will build a date with concatendation and extraction
functions. The bad news is that they will probably use a local date
format instead of ISO-8601 style.
The UNIX method uses extraction functions and temporal math -- go to
the first of this month and subtract 1 day. Since Sybase/SQL Server
use a floating point number for date time, it can be pretty fast for
one-at-time processing.|||SELECT DATEADD(d,- 1,CAST(CONVERT(char(6),CURRENT_TIMESTAMP
,112) + '01'
as smalldatetime))
Here are a few other little tricks too:
http://sqlnerd.blogspot.com/2005/06...sql-tricks.html
*mike hodgson*
http://sqlnerd.blogspot.com
Peter Newman wrote:

>ive been trying very unsucessfully to try and get the last date of the
>previous month,
>ie if i run the query today i want the result to be 31/01/2006
>
>|||Why go to all the effort and extra storage of building a calendar table when
you can simply use one of SQL Servers built in functions...
dateadd( day, -1, cast( '1 ' + datename( month, getdate() ) + ' ' +
datename( year, getdate() ) as smalldatetime ) )
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138838149.462000.53340@.o13g2000cwo.googlegroups.com...
> Have you looked up how to use a Calendar table? The table could
> include a code for last (business?) day of each month, so you can pull
> out a set of such dates for reports. I
> Your other options are computational. There is the "COBOL method" and
> the "UNIX method". The Cobol guys think of dates as character strngs,
> and they will build a date with concatendation and extraction
> functions. The bad news is that they will probably use a local date
> format instead of ISO-8601 style.
> The UNIX method uses extraction functions and temporal math -- go to
> the first of this month and subtract 1 day. Since Sybase/SQL Server
> use a floating point number for date time, it can be pretty fast for
> one-at-time processing.
>|||>>Why go to all the effort and extra storage of building a calendar table wh
en you can simply use one of SQL Servers built in functions...<<
1) Portability. Temporal functions vary quite a bit from product. Did
you mean to avoid using the Standard CURRENT_TIMESTAMP with the
proprietary getdate()?
2) Standards conformance. Did you notice that you assumed the date
format is "dd-mm-yyyy" because you are British? An American would
assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
'yyyy-mm-dd" is the only format allowed in the ISO standards
3) Your code will have problems with the time part of a DATETIME. the
last day of a month should end at the last possible fraction of a
second to be sure to catch everything. The standard SQL version would
be:
CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
|| EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
|| '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
4) The Calendar table is a more general tool for other temporal
problems. For a small extra effort now, he can solve a lot of problems
in the future.
Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
/ <<insert cowboy coding fad here>>. the current batch programmers
think only in terms of the immediate problem. The results are bad.
It is not just duplicated code that cannot be found to be refactored.
It is a lot of code that does the same task in many different ways.
Arrrgh!|||I'm with Joe on this one. Solve the problem with data rather than
code. Then you can get the last day of last month, the last business
day of last month, the first day of this month, the first business
day... and so on.
Best
Payson
Peter Newman wrote:
> ive been trying very unsucessfully to try and get the last date of the
> previous month,
> ie if i run the query today i want the result to be 31/01/2006|||> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
Oh dear, not checked the product specs lately?
I use the format DD MMMMMMMM YYYY, example 1 February 2006; so there is NO
confusion at all. Also, I remove the time component entirely and reset back
to midnight on casting to smalldatetime
print '1 ' + datename( month, getdate() ) + ' ' + datename( year,
getdate() )
Even a beginner would **know** to look at the manual and not guess how
things work, how come you make such fundemental mistakes?

> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
That was not the posters requirement, they required a date without the time
component, which we don't have in SQL Server, so within SQL Server we use
00:00:00.000 for when no time component is required - again, even a beginner
would **know** that.
The syntax you give doesn't even work in SQL Server and as this is a
MICROSOFT SQL SERVER forum I would appreciate it if you at least point out
the differences when you post your unusable rubbish.

> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
A very big overhead for a simple problem, again, stop bloating up the design
to make more money. Keep things Simple Sweet and the design will be more
supportable and less likely to be buggy.

> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
Seriously, you need to get some real programming experience because you
don't seem to have any, either that or just stick to logical design and live
the programming to people who have been professionally trained and not
picked their knowledge up from reading books.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138908162.308682.73220@.f14g2000cwb.googlegroups.com...
> 1) Portability. Temporal functions vary quite a bit from product. Did
> you mean to avoid using the Standard CURRENT_TIMESTAMP with the
> proprietary getdate()?
> 2) Standards conformance. Did you notice that you assumed the date
> format is "dd-mm-yyyy" because you are British? An American would
> assume "mm-dd-yyyy" instead. A SQL programmer would ** know** that
> 'yyyy-mm-dd" is the only format allowed in the ISO standards
> 3) Your code will have problems with the time part of a DATETIME. the
> last day of a month should end at the last possible fraction of a
> second to be sure to catch everything. The standard SQL version would
> be:
> CAST (EXTRACT (YEAR FROM CURRENT_TIMESTAMP)
> || EXTRACT (MONTH FROM CURRENT_TIMESTAMP)
> || '01 23:59:59.99999' AS TIMESTAMP) - INTERVAL '01' DAY
> 4) The Calendar table is a more general tool for other temporal
> problems. For a small extra effort now, he can solve a lot of problems
> in the future.
> Unfortunately, I find that thanks to Agile / Extreme / Fast Cycle Time
> / <<insert cowboy coding fad here>>. the current batch programmers
> think only in terms of the immediate problem. The results are bad.
> It is not just duplicated code that cannot be found to be refactored.
> It is a lot of code that does the same task in many different ways.
> Arrrgh!
>|||I frequently use calendar table for calculations like '5 business days
later' or
'now many business daya are there between 15-jan-2006 and 25-jan-2006'.
Ignoring any theories, with calendar table my queries sometimes perform
better.
I don't take that for granted and like to re-check that I'm using
efficient queries from time to time.
In this particular case, I took 500K rows from a production table. I
ran this query:
set statistics time on
go
select min(dateadd( day, -1, cast( '1 ' + datename( month, order_dt ) +
' ' +
datename( year, order_dt ) as smalldatetime ) )) mmin, max(dateadd(
day, -1, cast( '1 ' + datename( month, order_dt ) + ' ' +
datename( year, order_dt ) as smalldatetime ) ) ) mmx
from orders
...
SQL Server Execution Times:
CPU time = 8922 ms, elapsed time = 17423 ms.
Then I built a calendar table:
create table month_calendar(num int, date_from datetime, date_to
datetime)
declare @.d1 datetime
declare @.d2 datetime
declare @.n int
set @.d1='19700101'
set @.d2='19700201'
set @.n=1
while @.d1 < '20060505'
begin
insert into month_calendar values(@.n, @.d1,@.d2)
set @.n=@.n+1
set @.d1 = @.d2
set @.d2 = dateadd(month, 1, @.d2)
end
go
select min(date_from), max(date_to) from month_calendar
select count(*) from month_calendar
create unique clustered index uci on month_calendar(date_from)
select * from month_calendar
then I ran another query:
select min(m.num), max(m.num)
--select count(*)
from month_calendar m join orders on date_from<=order_dt and
order_dt<date_to
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 1010 ms.
(both queries were rerun several times to account for warm/cold cache,
random fluctuations,
whatever else)
So for this particular scenario in this particular environment I'd
definitely go for a calendar table, if and only if the query is run
frequently.
I'm not saying calendar tables are the best choice in all the cases.
Any comments?