Suppose you’d like to select rows in an ExactTarget data extension created on the last day of the month. Here’s what I use:

select
de.createdDate
from [dataExtension] de
where 
dateadd(dd,datediff(dd,0,de.createdDate), 0) = 
dateadd(d, -1, dateadd(m,datediff(m,0,dateadd(dd,datediff(dd,0,getDate()), 0))+1,0))

The last portion of the where clause finds the first day of the next month and then subtracts one day.

Normally queries like this run in a daily automation.

For further reading:

• ExactTarget Query Activity
• T-SQL DATEDIFF function
• T-SQL DATEADD function
• SQL-Server find the last day of any month