It seems like you might have a (very) old transaction running.

See what this tells you:

USE tempdb;

DBCC OPENTRAN;

...hope you can work it out.

You really need to provide truly representative sample data if you want a full solution.

I once was getting horribly flawed data from FedEx (missing closing quotes, missing delimiters, etc.; genuinely not expected, given their rep).  They would NEVER fix the data.  We had to...

I think this is right.  Naturally you'll need to confirm for yourself, as I don't have any additional test data.

IF OBJECT_ID('tempdb.dbo.#data') IS NOT NULL
   ...

Could do this with recursion, from right to left, but for now I'll just do a simple cursor loop.  Hopefully that will perform well enough for what you need.  Naturally...

Or just make sure they send a string that is valid for conversion in the language they are using!

If I write '25 dek 2020' in English, then that's my error,...

You will NOT get dup rows from dup values in an IN clause.  Easy enough to verify:

CREATE TABLE #t1 ( col1 int NOT NULL )
INSERT...

Either you to do it by CASTing/CONVERTing one language at a time, or have them include a language code in the file.

You could, though, use CASE so that you could...

You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect...

You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.

You...

You don't have to use replace - the string will be converted (implicitly) to an integer for comparison so any leading/trailing blanks will not affect that conversion.

You can also...

In the original version, try putting brackets around the column name "Kolicina".  It looks like you might have a non-standard character in the name.

I'd stick with temp tables.  And it can't hurt -- and could help -- to use a PK in the temp table (or table variable).

DROP TABLE IF...

See if these calcs give you results more like what you want.

Edit: Note that the calcs are completely independent of any/all date and language and DATEFIRST setting and will work...

• This reply was modified 4 years, 11 months ago by ScottPletcher.

If you build nonclustered indexes as necessary,...