SELECT   *
FROM     MyTable
WHERE    CreatedDate = 'the date you are interested in'

SELECT   *
FROM     MyTable
GROUP BY CreatedDate
HAVING   COUNT(*) > 1

select
    'select * from '
        + t.name
        + ' where UpdateDate = ''2009-05-01''' as SelectStatement
from sys.tables as t

select
    'select * from ' + t.name
        + ' where ' + c.name + ' = ''2009-05-01''' as SelectStatement
from sys.tables as t join sys.columns as c on t.object_id=c.object_id
where c.system_type_id = (select system_type_id from sys.types where name = 'datetime')