SQL Server Error: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value

sofia
 
on May 29, 2021 07:51 AM
1258 Views

HI Dharmendr;

error is occure While running the code like:

'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code

Additional information: The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

my database table record is like that:

all the datatype value of column is nvarchar(50)

ipaddress             average     pingtime

192.168.15.167     25          25-5-2021 1:15AM

192.168.15.167     50         25-5-2021 2:30AM

192.168.15.167     70          25-5-2021 4:20AM

192.168.15.167     100         25-5-2021 7:30AM

192.168.15.167     50           25-5-2021 8:30PM

192.168.15.167     70           25-5-2021 9:20PM

192.168.15.167      100        25-5-2021 11:50PM

192.168.15.167       25         26-5-2021 1:00AM

192.168.15.167       50         26-5-2021 1:16AM

192.168.15.167       75          26-5-2021 2:20AM

192.168.15.167        100       26-5-2021 10:16AM

and so on.

        [WebMethod]
        public static List<object> GetChartData()
        {
            string query = "SELECT [pingtime],[average] FROM [dbo].[graph] WHERE ipaddress='192.168.15.167' AND pingtime BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE() ORDER BY pingtime";
            string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            List<object> chartData = new List<object>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            chartData.Add(new object[]
                    {
                        Convert.ToDateTime(sdr["pingtime"]), sdr["average"]
                    });
                        }
                    }
                    con.Close();
                    return chartData;
                }
            }
        }

SQL DESIGN

 INTO [dbo].[graph]
           ([ipaddress]
           ,[average]
           ,[pingtime])
     VALUES
           (<ipaddress, nvarchar(50),>
           ,<average, nvarchar(50),>
           ,<pingtime, nvarchar(50),>)
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on May 31, 2021 02:09 AM
on May 31, 2021 02:10 AM

Hi sofia,

You need to convert the pingtime to DateTime and then compare in where condition.

Refer below query.

SELECT [pingtime],[average] FROM [dbo].[graph] WHERE ipaddress='192.168.15.167' AND CONVERT(DATETIME,pingtime,103) BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE() ORDER BY pingtime

Code

[WebMethod]
 public static List<object> GetChartData()
 {
     string query = "SELECT [pingtime],[average] FROM [dbo].[graph] WHERE ipaddress='192.168.15.167' AND CONVERT(DATETIME,pingtime,103) BETWEEN DATEADD(DAY, -7, GETDATE()) AND GETDATE() ORDER BY pingtime";
     string constr = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
     List<object> chartData = new List<object>();
     using (SqlConnection con = new SqlConnection(constr))
     {
         using (SqlCommand cmd = new SqlCommand(query))
         {
             cmd.CommandType = CommandType.Text;
             cmd.Connection = con;
             con.Open();
             using (SqlDataReader sdr = cmd.ExecuteReader())
             {
                 while (sdr.Read())
                 {
                     chartData.Add(new object[]
                     {
                         Convert.ToDateTime(sdr["pingtime"]), sdr["average"]
                     });
                 }
             }
             con.Close();
             return chartData;
         }
     }
 }