SQL Server column type = Date not accepting dates as varchar in insert statemant
SQL Server, internally, stores dates in a binary representation. All dates from whatever source are in the same representation. So a date is a date is a date whether it originates in Paris, London or Stockholm.

Since humans aren't great at communicating in binary, by and large, we have to pass values to SQL Server in a form we can understand. This is generally text. The insert statement you show has a great many strings representing dates. Some are parameters to DATEADD, some are predicates. To be able to calculate and compare correctly the server must convert these strings and dates to a common representation. The rules of precedence say that will be the internal date representation.

In order to translate a string to a date SQL Server must know which part of the string to interpret as years, months, days etc. To do that it uses the culture of the current session. This information can be found from a DMV

select language, date_format
from sys.dm_exec_sessions
where session_id = @@spid;


For me this returns

language   date_format
---------- -----------
us_english mdy        


I have no problem running your insert statement. However, if I change it thus

set language British;

language  date_format
--------- -----------
British   dmy


I get your error. The same happens for set language French; because they share the dmy format. This leads to colaring the culprit for your error as the upper bounds on the WHERE and BETWEEN clauses. Either change the session settings to match your query (above or DATEFORMAT only) or vice versa, or use CONVERT with styles to tell SQL Server how to parse your strings.

I couldn't get it to work by changing the date columns to varchar. Don't know what's going on there.



There is a deeper point here, however. You jump through a great many hoops to get your insert data just right. This is wasted effort. No matter how the data is presented it is stored internally in a single way.

In particular those three "French" columns are all defined as date. Date is an internal representation. When you eventually select these rows back you'll find they're all presented in the same way.

What you're trying to achieve is date formatting not date storage. That is properly the responsibility of the UI or report-printing software. SQL Server does not store dates in any format. It has a binary representation.

That said, it does offer the CONVERT and FORMAT functions for when they must be used. You can use this and computed columns to achieve your outcome.

drop table if exists #t;

create table #t
(
    d date,
    FullDate as FORMAT(d, 'MMMM dd, yyyy'),
    MonthShortName as FORMAT(d, 'MMMM'),
    FrenchLongYear as FORMAT(d, 'MMM dd, yy')
);

insert #t(d) select GETDATE();

select * from #t;

d           FullDate        MonthShortName  FrenchLongYear
----------  --------------  --------------  --------------
2020-04-02  April 02, 2020  April           Apr 02, 20