Create job to send alert using sp_send_dbmail when table value greater than x

You can use convert (date, GETDATE()) to get just the 'date' part:

select GETDATE(), convert (date, GETDATE())
----------------------- ----------
2012-11-16 08:58:20.750 2012-11-16

And if you're only ever going to be comparing 'date' parts, it might be worth changing the datatype in your table to DATE, to save having to convert each time you query it.

For an IF statement you need to pass in a value to be checked, and then wrap the code to be run within BEGIN and END statements. Here's an example using the sysobjects table:

declare @cnt integer
select @cnt=COUNT(1) from sysobjects

if @cnt>50
begin
  select 'hello'
end

which will say hello if you've more than 50 objects in your DB

So in your case:

USE DATABASEname
go
declare @counter integer

select @counter=MAX([Counter]) from SMSCounter where convert(date,CounterDate) = convert(date,GETDATE())

IF @counter > 950
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
  @recipients=N'[email protected]',
  @body='1000 Daily SMS cap near',
  @subject ='1000 Daily SMS cap near',
  @profile_name ='SCOTT',
  @query =  '(select * from DATABASEname.dbo.SMSCounter where convert(date,CounterDate) = convert)(Date,GETDATE()))'
END

I've added a MAX function to your check query, as this will only return a single row containing the maximum value for that day, otherwise it'll return a random value from all the matching rows. If you're overwriting the day's value each time you write to the table, then it's superfluous.