Updating Date in Spreadsheet to table in SQL in UK date format

Hi, I have a couple of columns, once edited need to update a table in SQL. This is my code

Please Login or Register  to view this content.

However it is not taking UK date formats, it is assuming it is an american format, I get conversion of varchar data type to datetime results in an out-of-range value. What should I add to change the format? The format is in UK on the spreadsheet seemingly....

Hi,

By not taking I mean if I enter 31/12/2014 into I2 it will error, but if I enter 12/31/2014 it will run the update query. I changed the target to text and also tried to set the format, but I still get the error when entering a UK date.

Update, I changed all the dates via SQL to 31/12/2013. They appear on the spreadsheet as this date. However, once I change to 30/12/2013 I get the conversion of varchar data type, but if I enter 12/30/2013 it works fine.

To update/insert a date into a date field with SQL, the date must be between #s, not 's. ' is for string values., # is for dates.

Dates can be a pain, I've always found it's safest to convert a date to it's decimal value (41000 odd) and insert that instead, can't get it wrong then.

@Bellygas, that's only true for Access. SQL Server for example, stores datas as strings and must be passed in the format yyyy-mm-dd enclosed in single quotation marks

ahhhhh, didn't know that. Ta.

I am updating MS SQL 2008_R2. It's frustrating as they look in the UK format on the sheet, but when I change the date to 01/11/2013 it goes into SQL as 2013/01/11, which is wrong. As the actual day of the month doesn't matter too much for me, I have resorted to entering 02/02/2013 or 11/11/2013 so when I spit out the data again at least the month is correct. I would prefer an actual solution though!

I still get the same error :/