CODEWITHSUNDEEP
sqlsql-server
user33484
user33484

Reputation: 568

SQL Server : inserting datetime into table

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?

Upvotes: 1

Views: 22839

Answers (3)

Cenderze
Cenderze

Reputation: 1222

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.

Upvotes: 0

Michael Williams
Michael Williams

Reputation: 11

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)

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

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]

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

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

Upvotes: 1

Related Questions