do you have any DDL and sample data you could provide?  It's easier to work with real data than to look and say it should work

Just a thought, but instead of

IF NOT EXISTS(SELECT * ... or IF EXISTS(SELECT * ... , why not use Top 1?  all you need to know is only 1 exists,...

Can you provide ddl and sample data.  We don't have your tables and data

Thanks Matt, but the * doesn't work in some occasions.  I have multiple VatRates I'm trying to get and it only returns 1.  This should return all 6 Vat records

How about the table it is loading into?  Triggers, indexes???  Also, what kind of data are you moving? Is it large binary objects, all varchar(max) columns??  how long to do...

Is it just a straight BulkInsert command?  Format file?  Local or over the network?  how big is the file?  What about the table it is inserting into?  Indexes, triggers, etc...??? ...

You can always select all that data into a new table and see what the datatypes are.  It may clue you in on which column and row

You can try something like this

(DT_STR,4,1252) datepart("yyyy", getdate()) + Right("0" + (DT_STR,2,1252) datepart("mm", getdate()),2) + Right("0" + (DT_STR,2,1252) datepart("dd", getdate()),2) + "_" + Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) +...

Is the "YYYYMMDD_MMHH" part hard coded?

Looks like duty_saved is an alias in the first select and not the second

(([Chap 99 Rate] + [1_97 Duty_Rate]) * Proto_Value) as DUTY_SAVED

in the...

Did you setup the 2 tables and try it to see what results you would get?

I don't know how or what [date] is in your query, but these 2 fail. Can you provide sample date that works

select dateadd(d,getdate()-722815,'1980-1-1')
select dateadd(d,cast(getdate()...

Can you post DDL, sample data.  You will need to use a dynamic pivot to get what you want
You can use select EOMonth('1/22/2019') to get end of month

can you provide DDL, sample data and expected results?  Otherwise, I would break each part down to make sure it is returning what you are expecting.  Sounds like something is...