-3

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();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Harish
  • 13
  • 8
  • @DaleBurrell: Isn't it in the title? – Ken White Apr 30 '19 at 02:31
  • @KenWhite good point I missed that, although its not the actual error because it contains a spelling mistake. – Dale K Apr 30 '19 at 02:36
  • 2
    @Harish the error tells you the answer - you're converting the date to a string format that isn't recognised by the query engine. – Dale K Apr 30 '19 at 02:37
  • @Dale Burrell can you correct my query – Harish Apr 30 '19 at 02:48
  • Please assign `"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") + "' "` to a temporary variable **and include its value in your question** – mjwills Apr 30 '19 at 03:33

1 Answers1

1

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();

More on this here:

https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings#tSpecifier

How do I get the AM/PM value from a DateTime?

Gauravsa
  • 6,330
  • 2
  • 21
  • 30