0

I am running this query for updating the datetime column in my table:

DateTime today = DateTime.Today;
string[] queries = new string[] { "UPDATE *** SET ExpirationDate = " + today.ToString("yyyy/MM/dd"),
"UPDATE *** SET WrittenDate = " + today.ToString("yyyy/MM/dd"),
"UPDATE *** SET SystemDate = " + today.ToString("yyyy/MM/dd"),
"UPDATE *** SET SystemDate = " + today.ToString("yyyy/MM/dd"),
"UPDATE *** SET EventDate = " + today.ToString("yyyy/MM/dd")};

This format is based off of what I found in this thread: Operand type clash: int is incompatible with date + The INSERT statement conflicted with the FOREIGN KEY constraint

But it is not working. I know that they are datetime fields because I can successfully perform DATEADDs on them. I just want to set all the records in these columns to the current date, but I keep getting the same error even with all the different ways I've tried. Advance thanks for any help!

Bbylsma
  • 25
  • 6
  • 3
    You need to enclose date strings in single quotes. However, the "right" way to do this would be to take advantage of parameterized queries. – itsme86 Jun 14 '19 at 20:04

1 Answers1

0

The DATE Value in SQL must be placed in single quotes '

I am running this query for updating the datetime column in my table:

DateTime today = DateTime.Today;
string[] queries = new string[] { "UPDATE *** SET ExpirationDate = '" + today.ToString("yyyy/MM/dd") + "'",
"UPDATE *** SET WrittenDate = '" + today.ToString("yyyy/MM/dd") + "'",
"UPDATE *** SET SystemDate = '" + today.ToString("yyyy/MM/dd") + "'",
"UPDATE *** SET SystemDate = '" + today.ToString("yyyy/MM/dd") + "'",
"UPDATE *** SET EventDate = '" + today.ToString("yyyy/MM/dd") + "'"};

I am not sure about the "yyyy/MM/dd", I guess you should place it "yyyyMMdd".

Zeina
  • 1,573
  • 2
  • 24
  • 34
  • Well it stopped throwing the error, so thank you tons for that! But now even though it executes the nonQuerys, the dates don't change in the database (and it is yyyy/MM/dd for me) – Bbylsma Jun 14 '19 at 20:25
  • 1
    Well adding CURRENT_TIMESTAMP instead of the whole today.tostring() works in SQL server manager 2017, so it is something with my connection or the like. Thank you for the reminder on the single quotes! – Bbylsma Jun 14 '19 at 20:56