Suppose you’d like to select subscribers from an Salesforce Marketing Cloud data extension that were created in the last thirty days. Here’s what I use:

select
emailAddress
from [dataExtension]
where createdDate >= cast(dateadd(day, -1, getDate()) as date)
and createdDate <= cast(dateadd(day, -30, getDate()) as date)

or between two static dates:

select
emailAddress
from [dataExtension]
where s.createdDate >= cast('2013-03-19' as date)
and s.createdDate <= cast('2013-04-19' as date)

For further reading:

• Query Activity
• T-SQL dateadd() function