I'm trying to get data between two dates from SQL Server.

I try it with this code:

string kayit = "SELECT * FROM Kabin_Verileri WHERE Readtime BETWEEN '"   dateTimePicker1.Value   "' and '"   dateTimePicker2.Value   "'";

but it get this error:

System.Data.SqlClient.SqlException: 'The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.'

Error

My SQL Server table design

Does someone have an idea how to get data between two dates?

CodePudding user response：

You need to format the dateTimePicker1.Value and dateTimePicker2.Value to strings that sql can read as a date. ISO format 'yyyy-mm-dd' would be ideal for most sql engines.

in c# you do this...

string fromDateStringISO = Convert.ToDateTime(dateTimePicker1.Value).ToString('o');
string toDateStringISO = Convert.ToDateTime(dateTimePicker2.Value).ToString('o');

string kayit = "SELECT * FROM Kabin_Verileri where Readtime BETWEEN '"   fromDateStringISO   "' and '"   toDateStringISO    "'"

'o' format is ISO format. Check here

CodePudding user response：

You need to change varchar data type to a datetime. If dateTimePicker is datetime property you can write it down as dateTimePicker (no need to get its value).

string kayit = "SELECT * FROM Kabin_Verileri where Readtime BETWEEN '"   dateTimePicker1   "' and '"   dateTimePicker2   "'";

If dateTimePicker is not DateTime property then you need to convert it like @nazim said above