# in SQL Query

Question

I have somebody elses code (C# ASP.Net) which contains the following query:

string query = "SELECT distinct(destinations.name) as Destinations 
                FROM destinations, flights 
                WHERE destinations.d_ID = flights.d_ID 
                  AND flights.Date = #" + date.ToShortDateString() + "#";

I could not find why the # is required here before and after the date parameter. It could be passed as a normal string value (between ' and '). Is there any advantage of using # ?

Many Thanks, Ali

Answer 1

That is access syntax, are you sure you are connecting to sql server?

Answer 2

Is this against an access database? That platform requires dates be bracketed by the "#"

Edit: to answer your main question, no, unless it is Access where it would be required, there is no benefit of leaving the hash marks in the query as is.

Answer 3

There is nothing wrong with the pound symbol, but it is not ANSI compliant SQL. You are much better off rewriting the query with a parameter for the date.

ex:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT distinct(destinations.name) as Destinations FROM destinations, flights WHERE destinations.d_ID = flights.d_ID AND flights.Date = @dateparm";
cmd.Parameters.Add(new SqlParameter("dateparm",value);
SqlDataReader rdr = cmd.ExecuteReader();