Error: Conversion failed when converting datetime from character string .NET SQL Server

Question

I realise this question has been asked before, but after going through the previously answered questions, i still can't quite figure out what's wrong with this code.

FYI I am in the UK.

   public static void GetDataForCSEP(string viewName, string schemaName, 
                                             string dateFieldName, DateTime startDate, DateTime endDate)
        {

            string dateFormat = "yyyy/MM/dd HH:mm:ss";


            //Connect to SQl Server 

            string commandText = "SELECT * FROM " + schemaName + "." + viewName + " WHERE @dateFieldName BETWEEN @startDate AND  @endDate";


            using (SqlCommand sqlCmd = new SqlCommand(commandText,sql_Conn))
            {
                sqlCmd.CommandType = CommandType.Text;

                sqlCmd.Parameters.Add("@dateFieldName",SqlDbType.NVarChar, 30).Value = dateFieldName;
                sqlCmd.Parameters.Add("@startDate", SqlDbType.DateTime).Value = DateTime.Parse(startDate.ToString(dateFormat));
                sqlCmd.Parameters.Add("@endDate", SqlDbType.DateTime).Value = DateTime.Parse(startDate.ToString(dateFormat));



                sql_Conn.Open();
                sqlCmd.ExecuteNonQuery();


            }
}

Why on earth are you doing DateTime.Parse(startDate.ToString(dateFormat)) - startDate is already a DateTime value. Why convert it to string just to convert it back? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jul 26, 2011 at 13:33

Pranay Rana · Accepted Answer · 2011-07-26 13:31:37Z

2

On Error is you cannot pass column name as parameter

    "SELECT * FROM " + schemaName + "." + viewName + " 
WHERE @dateFieldName BETWEEN @startDate AND  @endDate";

this should be

    "SELECT * FROM " + schemaName + "." + viewName + " 
WHERE " + dateFieldName  + " BETWEEN @startDate AND  @endDate";

To avoid sqlInjection attack make use of Sp_executeSQL to execute this type of query, because this is dynamic sql query.

answered Jul 26, 2011 at 13:31

Pranay Rana

177k37 gold badges244 silver badges267 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MatBailie Over a year ago

Agreed, as structured the code will attempt to check whether the Static String @dateFieldName is between two dates. To do this the SQL Engine is attempting to convert the text value of @dateFieldName into a date, and failing.

Widor · Accepted Answer · 2011-07-26 13:30:50Z

1

It will depend on your SQL Server locale settings, but perhaps

string dateFormat = "dd-MMM-yyyy HH:mm:ss";

will work for you?

answered Jul 26, 2011 at 13:30

Widor

13.3k7 gold badges47 silver badges67 bronze badges

2 Comments

MrBliz Over a year ago

THanks, how do i find out those settings? It's login specific isn't it?

Widor Over a year ago

Yes and no - there'll be defaults, but you can also modify them for the duration of a user's connection etc. I'd check Books Online, as I'm not so familiar with the DBA side!

Collectives™ on Stack Overflow

Error: Conversion failed when converting datetime from character string .NET SQL Server

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related