System.Data.SqlClient.SqlException (0x80131904)) conversion data or time from character to string

Question

System.Data.SqlClient.SqlException (0x80131904)) conversion data or time from character to string

I am trying to filter data between two dates; today 12:00 am to tonight at 12:00 pm. But I am getting this error:

System.Data.SqlClient.SqlException (0x80131904)) conversion data or time from character to string

Code:

DateTime today = DateTime.Today;
string fileNameBase = today.ToString("dd-MMM-yyyy 12:00:00 AM");

ExportToExcel export = new ExportToExcel();
export.ExportTable("SELECT daytime, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, BSNO, Spare1, COLUMN_5, COLUMN_6, Spare2, DEG, TEMP3, Spare3, CAS1, CAS2, ACTIVA, Spare4, CAS3, CAS4, PHOS, Spare5, Spare6, Spare7, TEMP2, CAS5, CAS6, DRY, TEMP1 FROM TABLE_2 WHERE daytime BETWEEN '" + (today.ToString("dd-MMM-yyyy 12:00:00 AM")) + "' AND '" + today.ToString("dd-MMM-yyyy 12:00:00 PM") + "'  ");

export.SaveWorkbook();
export.shutDown();

c#

sql-server

asked on Stack Overflow Apr 30, 2019 by

Harish • edited Apr 30, 2019 by

marc_s

1 Answer

You can do this:

DateTime today = DateTime.Today;
string dateAMFormat = today.ToString("dd-MMM-yyyy hh:mm:ss tt", CultureInfo.InvariantCulture));
string datePMFormat = today.AddHours(12).ToString("dd-MMM-yyyy hh:mm:ss tt", CultureInfo.InvariantCulture));

ExportToExcel export = new ExportToExcel();
export.ExportTable("SELECT daytime, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, BSNO, Spare1, COLUMN_5, COLUMN_6, Spare2, DEG, TEMP3, Spare3, CAS1, CAS2, ACTIVA, Spare4, CAS3, CAS4, PHOS, Spare5, Spare6, Spare7, TEMP2, CAS5, CAS6, DRY, TEMP1 FROM TABLE_2 WHERE daytime BETWEEN '" + dateAMFormat + "' AND '" + datePMFormat + "'  ");
export.SaveWorkbook();
export.shutDown();