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: