MS-SQL datetime to char conversion error

[expired user #8440]'s profile image [expired user #8440] posted 9 years ago in General Permalink
When trying to delete a row with a DATETIME column in it, HeidiSQL generates SQL like that:

DELETE FROM "database"."schema"."table" 
WHERE "id"=123 AND "datetimecolumn"='21/11/2014 00:00:00'


This raises the following error:

"SQL error (3621): The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. The statement has been terminated."

This also happens when trying to import another HeidiSQL SQL export. Looks like the general handling of DATETIME values for MS-SQL is wrong.
ansgar's profile image ansgar posted 9 years ago Permalink
That date/time format should have been some universal format, running on various MS SQL versions. I got several bugreports from users of older server versions before. Which one is your version?
[expired user #8440]'s profile image [expired user #8440] posted 9 years ago Permalink
I'm forced to use MS-SQL 9.00.5000.00 (SQL Server 2005) for a customer.

This version has also issues with other MS-SQL features, like SQL export. For instance, it doesn't support INSERT row construction like

INSERT INTO table (ID, Value) VALUES (1, 'First'), (2, 'Second'), (3, 'Third');


Instead, one has to do it like that:

INSERT INTO table (ID, Value)
SELECT 1, 'First'
UNION ALL
SELECT 2, 'Second'
UNION ALL
SELECT 3, 'Third';


Unfortunately, this version is still very popular among many customers.
[expired user #8440]'s profile image [expired user #8440] posted 9 years ago Permalink

This version has also issues with other MS-SQL features, like SQL export.



I meant "...with other HeidiSQL features,..." of course. ;)
ansgar's profile image ansgar posted 9 years ago Permalink
How does the DELETE FROM query have to look like for your MS SQL version?
[expired user #8440]'s profile image [expired user #8440] posted 9 years ago Permalink
Sorry for the delay, I was with another project for some days.

The problem is just the date format. It has to be "2014-11-21" instead of "21/11/2014". I didn't find any HeidiSQL configuration options for this, so I'm not sure how I can get HeidiSQL to generate date values in this format.

Please login to leave a reply, or register at first.