gabe matteson

Does this part of the query look correct? It's formated so that it doesn't
use the time feature and zero's it out to midnight...

where datecreated between
DATEADD(m,-1,CAST(CONVERT(CHAR(10),GETDATE(),120)as DATETIME)) and GETDATE()
order by datecreated

how would i create a query that returns all the rows with the exception of
the past month....? thank you!

dan guzman

This looks OK to me as long as you want the last 28-31 days or so of data,
including the current day. However, you might consider using
DATEADD/DATEDIFF to remove the time portion of datetime:

DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))


If you want to return only data older than the last 28-31 days, use '<':

WHERE
datecreated <
DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))), GETDATE()

To return only data older than the start of last month:

WHERE
datecreated <
DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)


--
Hope this helps.

Dan Guzman
SQL Server MVP

dan guzman

Oops - copy/paste error. This should have been:

WHERE
datecreated <
DATEADD(m,-1,DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())))

--
Hope this helps.

Dan Guzman
SQL Server MVP

gabe matteson

Thank you, very helpful!