SQL Server : inserting datetime into table

Question

I have a table:

CREATE TABLE [dbo].[667788]
(
    a NVARCHAR(100),
    b NVARCHAR(100),
    c NVARCHAR(100),
    d NVARCHAR(100),
    e NVARCHAR(100),
    f NVARCHAR(100),
    t1 DATETIME,
    t2 DATETIME
)

I'm trying to insert the following values:

('x','y','m','2','a','c','16/11/2012 00:00:00','06/08/2013 00:00:00'),

but I get an error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Does anyone know any way around it in the format I have provided?

https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in — Naveen Margan, May 31 '17 at 16:49
Always format your datetimes using [ISO8601](https://en.wikipedia.org/wiki/ISO_8601) notation to avoid ambiguity. — Igor, May 31 '17 at 16:49
Possible duplicate of [SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value](https://stackoverflow.com/questions/20838344/sql-the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in) — hardkoded, May 31 '17 at 16:49
And if you're using SQL Server **2008** or newer, I'd recommend using `DATETIME2(n)` instead of the plain old clunky `DATETIME` in your tables — marc_s, May 31 '17 at 18:21

score 1 · Accepted Answer · edited Jul 29 '20 at 18:30

The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime, are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn]

Bad habits to kick : mis-handling date / range queries - Aaron Bertrand

You could probably get away with just setting set dateformat dmy before your insert though.

set dateformat

rextester demo using set dateformat dmy;: http://rextester.com/NUQM21818

score 1 · Answer 2 · answered May 31 '17 at 20:59

Similarly to Cenderze's answer, you can perform your insert in one shot by inserting the values using a select statement that is performing a convert on the values that are being troublesome:

CREATE TABLE [dbo].[WRBIEL_MasterBindersv2]
(
    a NVARCHAR(100),
    b NVARCHAR(100),
    c NVARCHAR(100),
    d NVARCHAR(100),
    e NVARCHAR(100),
    f NVARCHAR(100),
    t1 DATETIME,
    t2 DATETIME
)

INSERT INTO [dbo].[WRBIEL_MasterBindersv2]
SELECT
'x','y','m','2','a','c',CONVERT(datetime, '16/11/2012 00:00:00', 103),CONVERT(datetime,'06/08/2013 00:00:00' , 103)

score 0 · Answer 3 · edited Sep 19 '17 at 08:35

Don't know if this is possible and cant verify at the moment but you could try to use

 select 
 cast (a as nvarchar(100)) as a,
  ...
Convert(datetime, getdate(),103)  as t1
 into yournewtable
 From youroldtable
 Where 1=2

If you cast your columns into the desired data types and create a new table using Insert ... Into .. From and adding where 1=2 you make a table with your desired datatypes and column names.

From what I've gathered using convert with the parameter 103 ought to give your desired data type result. You may also try to use convert with varchar instead of datetype.

SQL Server : inserting datetime into table

3 Answers3