PHP Variable in SQL LIKE
The syntax would be along the lines of:

$adate =  date('Y-m-d');
$tvisit = db_result(db_query("SELECT counter_id FROM {counter_today} WHERE counter_date LIKE '%s%%' ORDER BY counter_id DESC LIMIT 1", $adate));


The changes from your original:


Put {}'s around table name so if you are on a shared database, drupal can put a prefix on automagically.
Put LIKE's % in the sql itself, not in the variable.  You need to put in %% since % itself is drupal's way of starting a variable (kinda like \\ to put in a \)
Put %s in in front of that for the variable, which is a string.  Remove the {}'s since they are for table names.  Granted, php will find $adate in there since it's all wrapped in ""'s but then you open up yourself to sql injection etc (however remote it would be in this specific example).
Put the $adate variable in the function call as an argument.
Take out the ``'s surrounding the field/column names.