Out of Range Error while accessing date from SQL Server

Question

I want to fetch the maximum value of date from the table. But I always get an 'OutofRangeException' error. I changed my query several times.

Due_Date column has date data type
Due_Date_Sample is a string var
Due_Date_var is a DateTime var

My code:

using (SqlConnection sqlCon = new SqlConnection(Main.connectionString))
{
    string commandString = "SELECT TOP 1 FORMAT(Due_Date,'dd-MM-yyyy') FROM Transactions WHERE Plot_Code='" + Plot_Code_var + "' ORDER BY Due_Date DESC;";

    SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);

    sqlCon.Open();

    SqlDataReader dr = sqlCmd.ExecuteReader();

    while (dr.Read())
    {
        date_control_var = 2;
        Due_Date_Sample = (dr["Due_Date"].ToString());
        Due_Date_var = DateTime.Parse(Due_Date_Sample.ToString());
    }

    dr.Close();
}

Check to see if it is null. The database excepts a null for DateTime but c# doesn't. — jdweng
– jdweng, Commented Jan 30, 2019 at 4:09
I am checking for null in another try catch.... This piece of code runs when the value is not null — Taha Waseem
– Taha Waseem, Commented Jan 30, 2019 at 4:10
Please, also consider using parameters instead of string concatenation in your SQL. — yv989c
– yv989c, Commented Jan 30, 2019 at 4:19

Kirill Polishchuk · Accepted Answer · 2019-01-30 04:11:38Z

7

You need to give an alias to the selected value, e.g.: FORMAT(Due_Date,'dd-MM-yyyy') as Due_Date

answered Jan 30, 2019 at 4:11

Kirill Polishchuk

56.4k11 gold badges125 silver badges127 bronze badges

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

Comments

Gauravsa · Accepted Answer · 2019-01-30 04:19:31Z

You can do this:

using (SqlConnection sqlCon = new SqlConnection(Main.connectionString))
                    {
                        string commandString = "SELECT TOP 1 FORMAT(Due_Date,'dd-MM-yyyy') as Due_Date FROM Transactions where Plot_Code='" + Plot_Code_var + "' ORDER BY Due_Date DESC;";

                        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
                        sqlCon.Open();
                        SqlDataReader dr = sqlCmd.ExecuteReader();
                        while (dr.Read())
                        {
                            date_control_var = 2;
                            Due_Date_Sample = (dr["Due_Date"].ToString());
                            Due_Date_var = DateTime.Parse(Due_Date_Sample.ToString());

                        }
                        dr.Close();
                    }

Collectives™ on Stack Overflow

Out of Range Error while accessing date from SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related