• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

How to supply a string for a datetime column in ADO.net

I want to write a select statement in which I supply a string value for a timestamp column (in Sybase Sql Anywhere  database)

For example:
string dateTimeStr = "2009-04-27 12:34:23";
string selectStr = " Select * from Table where DateTimeColumn =' "+dateTimeStr+" ' ";

When I execute this select statement, the Odbc driver gives an error like :
Can not convert ... to timestamp.
When I use the OdbcParameterCollection of the command and add a c# DateTime type value as the parameter to the select statement, the command executes without error.
Isn't there another way other than using parametres?
It would be really good if I could give a string in the where condition..
Thanks in advance..
2 Solutions
I would recommend always using parameters. It may seem cumbersome at first but it far easier for readability, especially coming back to commands you've written 6 months ago.

The problem is that while some servers can implicitly convert a string to a datetime or timestamp value, other may not (I can't comment on Sybase as I've only used it a handful of times for our phone system), there may be issues with localisation on the server.
The following code will work for what you need, in addition it has the benefit protecting you from SQL injected.  You will need to add System.Data and System.Data.SqlClient to your USING's.

            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnl;
            sqlCommand.CommandText = "SELECT * FROM Table WHERE FieldName = @FieldValue";
            sqlCommand.Parameters.Add("@FieldValue", SqlDbType.DateTime).Value = DateTime.Parse("12/1/2009 13:34:20");
            SqlDataReader drRecords = sqlCommand.ExecuteReader();

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now