SQL Server column type = Date not accepting dates as varchar in insert statemant

Question

In T-SQL, I am trying to insert values in a date table, US & French dates. When table is created with columns as DATE, the insert does not work, it errors.

When I switch to VARCHAR(12) instead of DATE for those columns, INSERT works fine

My server is windows 2012, english Microsoft SQL Server 2016 (SP2-CU10) (KB4524334) - 13.0.5492.2 (X64), regional settings France I googled but not able to find a solution.

Here are 3 scripts, 1st one is the table with columns with DATE type (with which insert statement fails)

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [date] NULL,
[FrenchUSLongYear] [date] NULL,
[FrenchLongYear] [date] NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

Here is the insert statement:

INSERT dbo.DimDateTest
( ShortDate, FullDate, MonthShortName, MonthLongName, FrenchShortYear, FrenchUSLongYear, FrenchLongYear, DateAge)
SELECT CAST(DATEADD(dd, Number.n-1, '1/1/2015') AS DATE)
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(DATEPART(dd, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(2)) + ', ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4)) 
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10))
,CAST(DATENAME(mm, DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(10)) + ' ' + CAST(YEAR(DATEADD(dd, Number.n-1, '1/1/2015')) AS VARCHAR(4))
, CONVERT(date, CAST(DATEADD(dd, Number.n-1, '1/1/2015') as datetime), 5) as FrenchShortYear
, FORMAT( CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 'dd/MM/yyyy', 'fr-FR' ) AS 'FrenchUSLongYear'
, CONVERT(VARCHAR(12), CAST(DATEADD(dd, Number.n-1, '1/1/2015') as date), 7) as 'FrenchLongYear'
,
CASE WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '1/1/2015' AND '3/31/2015' THEN 'Age of Goats'
WHEN DATEADD(dd, Number.n-1, '1/1/2015') BETWEEN '4/1/2015' AND '4/30/2015' THEN 'Age of Copper'
ELSE 'Age of the Unknown'
END
FROM dbo.Number
WHERE DATEADD(dd, Number.n-1, '1/1/2015') <= '4/30/2020'
ORDER BY Number.n

and here is the error I am getting

Msg 241, Level 16, State 1, Line 13 Conversion failed when converting date and/or time from character string.

When I drop and recreate the table by switching Columns type from DATE to varchar(12), insert works fine, here is the script for the table

CREATE TABLE [dbo].[DimDateTest](
[ShortDate] [date] NULL,
[FullDate] [varchar](20) NULL,
[MonthShortName] [varchar](10) NULL,
[MonthLongName] [varchar](15) NULL,
[FrenchShortYear] [varchar](12) NULL,
[FrenchUSLongYear] [varchar](12) NULL,
[FrenchLongYear] [varchar](12) NULL,
[DateAge] [varchar](20) NULL
) ON [PRIMARY]
GO

NOTE : dbo.Number is a number table populated by numbers from 1 to 10000

What am I doing writing wrong? Thanks for your help

Answer 1

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