CODEWITHSUNDEEP
c#asp.netsql-serverdatedatetime
Zaki Ahmad Sahri
Zaki Ahmad Sahri

Reputation: 37

String was not recognized as a valid DateTime [Remove Time from DateTime]

I'm retrieving a date from my sql server which is 2016-01-06(year-month-day) into a string. The sql format is Date. Instead of getting 2016-01-06 only, I will get 1/6/2016/(month/day/year) 12:00:00 AM . Now what I'm trying to do is remove the time and convert the date in dd/M/yyyy format. I've followed all the example in the other questions but I will get "String was not recognized as a valid DateTime" Is there any steps I missed or I'm doing it wrongly.

 protected void btnUser_Click(object sender, EventArgs e)
        {
            {
                string Name = cmbName.Text;
                string start = "";

                SqlConnection myConn = new SqlConnection("Data Source=localhost;" + "Initial Catalog=IBBTS_DB; Integrated Security =SSPI");

                    SqlCommand retrieveStart_DateCmd = new SqlCommand("SELECT startDate FROM testSet where TS_ID = 121 ;", myConn);
                    SqlDataReader reader6 = retrieveStart_DateCmd.ExecuteReader();
                    while (reader6.Read())
                    {
                        start = (reader6.GetValue(0).ToString());
                    }
                    reader6.Close();  

                    DateTime dateTime = DateTime.ParseExact(start, "dd/MM/yyyy", CultureInfo.InvariantCulture);


             }
        }

Upvotes: 1

Views: 1244

Answers (4)

Amar
Amar

Reputation: 417

Try assigning date in this format:

protected void btnUser_Click(object sender, EventArgs e)
{ 
        string Name = cmbName.Text, start = string.empty;

        SqlConnection myConn = new SqlConnection("Data Source=localhost;" + "Initial Catalog=IBBTS_DB; Integrated Security =SSPI");

        SqlCommand retrieveStart_DateCmd = new SqlCommand("SELECT startDate FROM testSet where TS_ID = 121 ;", myConn);
        SqlDataReader reader6 = retrieveStart_DateCmd.ExecuteReader();

        while (reader6.Read())
        {
              start = (reader6.GetValue(0).ToString());
        }
        reader6.Close();  

        DateTime dateTime = DateTime.ParseExact(start, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);//DateTime.ParseExact(start, "dd/MM/yyyy", CultureInfo.InvariantCulture); 
}

And in your Web.config file under add this

<configuration>
    <system.web>
         <globalization culture="en-US" uiCulture="en-US" />
    </system.web>

Upvotes: 0

Nico
Nico

Reputation: 12683

SQL Stores the datetime data type dates in a standard format for SQL. However when you retrieve a datetime data type from SQL the datetime result will be converted to the DateTime struct in C#.

As the retrieved value IS a DateTime object you dont need to convert to a string then parse it out. Doing this doubles the conversion work for retreiving that datetime.

Try this example.

protected void btnUser_Click(object sender, EventArgs e)
    {
        {
            string Name = cmbName.Text;
            DateTime start = default(DateTime);

            SqlConnection myConn = new SqlConnection("Data Source=localhost;" + "Initial Catalog=IBBTS_DB; Integrated Security =SSPI");

                SqlCommand retrieveStart_DateCmd = new SqlCommand("SELECT startDate FROM testSet where TS_ID = 121 ;", myConn);
                SqlDataReader reader6 = retrieveStart_DateCmd.ExecuteReader();
                while (reader6.Read())
                {
                    start = (DateTime)reader6[0];
                }
                reader6.Close();  
                string myFormattedString = start.ToString("dd/MM/yyyy");
         }
    }

Upvotes: 3

bri
bri

Reputation: 3030

Since you know that the field is going to be a date, and it's just the one field, can you just use GetDateTime?

DateTime dateTime = Reader6.GetDateTime(0)

That might let you skip string conversions. Obviously, check for dbNull's first if that's a possibility.

Upvotes: 1

dsz
dsz

Reputation: 5202

Avoid using ToString() - you are dumbing down the type that SQL Server is sending you. You should be able to retrieve the value using GetDateTime(0) instead of GateValue(0). That will allow you to manipulate the value more easily.

C# does not come with a built-in Date type, only DateTime. Hence this is what you are presented with. Having gotten a DateTime, you can use the normal Format operations to format it as you like.

Upvotes: 0

Related Questions