Problems with Getting a Date (range) -- sql server data mining

Atley
3/5/2004 12:19:24 PM

I am trying to get a date range from the first of whatever month is previous
to the current one to exactly one year previous to that date ie 02/01/04 to
02/01/03 to pull the appropriate section of data out...

I have tried to use:

BETWEEN MONTH(GETDATE()) - 1 + '/' + 01 + '/' + YEAR(GETDATE()) -1 AND
MONTH(GETDATE()) - 1 + '/' + 01 + '/' + YEAR(GETDATE())

But I get a Can't Convert to type INT even if i run this in a query as it's
own result...

I can't seem to find in the documentation what I am doing wrong... I am sure
that it is something simple.

Any help is appreciated.

Steve Kass
3/5/2004 3:11:28 PM

Atley,

Here is one solution:

select
dateadd(month, datediff(m,0, getdate())-1,0) as LastFirst,
dateadd(month, datediff(m,0, getdate())-13,0) as aYearBefore

This works because in SQL Server, 0 is a first-of-a-month date
(1900-Jan-01). You can make it more readable if you replace each of the
4 zeroes with a language-insensitive string representing the first of
any month of any year, like '1952-07-01T00:00:00.000'

SK

[quoted text, click to view]

John Gilson
3/5/2004 7:58:03 PM

[quoted text, click to view]

SELECT DATEADD(YEAR, -1, D.month_back - DAY(D.month_back) + 1),
D.month_back - DAY(D.month_back) + 1
FROM (SELECT DATEADD(MONTH, -1, CURRENT_TIMESTAMP)
AS month_back) AS D

--
JAG

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server data mining

sql server data mining : Problems with Getting a Date (range)