Date Range for SQL 2005

I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:

How to get the last day of the previous month? And the last day of the
current month?

SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')

Thanks,
AaronAaron (odysseus183@.hotmail.com) writes:

Quote:

Originally Posted by

I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')

SELECT dateadd(DAY, -1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01')

SELECT dateadd(DAY, -1,
dateadd(MONTH, 1, convert(char(6), CURRENT_TIMESTAMP, 112) + '01'))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||How to get the last day of the previous month? And the last day of the

Quote:

Originally Posted by

current month?

Below alternative to the technique Erland posted. Although it's not as
clear as building a date string, it performs better in situations where
dates are calculated for each row. The overhead of converting to/from
date/string can add up for large tables.

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

SELECT
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1,
0))

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aaron" <odysseus183@.hotmail.comwrote in message
news:1184361210.176551.223620@.n2g2000hse.googlegro ups.com...

Quote:

Originally Posted by

>I was reading the article and was unable to get some of queries to
execute. Can anyone help get these to work:
>
How to get the last day of the previous month? And the last day of the
current month?
>
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'19991231')
SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
'20000131')
>
Thanks,
Aaron
>

|||On Fri, 13 Jul 2007 14:13:30 -0700, Aaron wrote:

Quote:

Originally Posted by

>I was reading the article and was unable to get some of queries to
>execute. Can anyone help get these to work:
>
>How to get the last day of the previous month? And the last day of the
>current month?
>
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'19991231')
>SELECT DATEADD(month, DATEDIFF(month, '20000101', CURRENT_TIMESTAMP),
>'20000131')

Hi Aaron,

When I copy and paste these queries into SSMS, I get the desired
results: June 30 and July 31. Your request for help to get them to work
implies that you got different results. Could you expand on that?

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