Inserting date in sql server

Question

When I try to execute this query

INSERT  INTO StateRegion
        ( FullName ,
          Abbreviation ,
          RegionType ,
          Admitted ,
          Capital
        )
VALUES  ( 'Alabama' ,
          'AL' ,
          1 ,
          '1819-Dec-14' ,
          'Montgomery'
        );

it gives me error sql date conversion error :

Conversion failed when converting date and/or time from character string

Admitted is a Date type.

The issue is I can not change this format : 1819-Dec-14, is it possible to add convert method to the query above ?

Table definition :

CREATE TABLE StateRegion
(
  ID            bigint       PRIMARY KEY IDENTITY(1,1),
  FullName      varchar(50)  NOT NULL,
  Abbreviation  varchar(2)   NOT NULL,
  RegionType    smallint     NOT NULL,
  Admitted      date         NULL,
  Capital       varchar(50)  NULL
);

What is the table definition and the default language of the login? — Martin Smith
– Martin Smith, Commented Nov 21, 2014 at 19:43
Well Dec isn't a valid French month name. Can you change the default language of the login or issue a set language english? — Martin Smith
– Martin Smith, Commented Nov 21, 2014 at 19:49
@Souregi So, you couldn't change the format, but you could change how to write Dec? — Lamak
– Lamak, Commented Nov 21, 2014 at 19:54

Martin Smith · Accepted Answer · 2014-11-21 20:04:28Z

6

The month name part of that date format is interpreted according to the language of the login.

You can change the default language of the login to US English or British English if you must work with that format or issue a

  Set language english

To set the format at run time before the problematic query then optionally switch it back afterwards.

If you have the choice using yyyy-mm-dd or yyyymmdd would be preferable formats for date literals though that both avoid this issue when casting to date.

answered Nov 21, 2014 at 20:04

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitch · Accepted Answer · 2014-11-21 19:51:21Z

Use a parameterized query. Parameterization will send the date to the server in binary, avoiding any string conversions which depend upon the client locale.

Example in C#:

SqlCommand sqc = new SqlCommand("INSERT INTO MyTable (DateColumn) VALUES (@date)", con);
sqc.Parameters.AddWithValue("@date", new DateTime(1819, 12, 14));

If you are running this from an interactive batch (SQL Server Management Studio, or similar), use SET LANGUAGE to ensure the dates are parsed correctly:

SET LANGUAGE ENGLISH;

INSERT INTO StateRegion (FullName, Abbreviation, RegionType, Admitted, Capital)
VALUES ('Alabama', 'AL', 1, '1819-Dec-14', 'Montgomery');

SqlFiddle example showing correct parsing

The question isn't tagged with C#, why giving a solution using it?
@Lamak, because it is the most common client for SQL Server if it is not being done through an interactive UI.

Stephen Bodine · Accepted Answer · 2014-11-21 20:00:59Z

Do not confuse storage format and display format. Just because the server store the date in the database as '1819-12-14' you can use a custom formatting output for the display. Then correct for the display issue with a function such as:

CREATE FUNCTION usp_FormatedDateString (@Date Date)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE @RETURN AS VARCHAR(50)
    DECLARE @I INT = 0
    DECLARE @M AS VARCHAR(100) = 'JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC'
    SET @RETURN = CAST(DATEPART(YEAR,@Date) AS VARCHAR(4)) 
    SET @I = DATEPART(MONTH, @Date) - 1
    SET @RETURN = @RETURN + '-' + SUBSTRING(@M,(@I*3)+1,3)+'-'+ CAST (DATEPART(DAY,@Date) AS VARCHAR(2))
    RETURN @RETURN
END
GO

Then when you display the results:

SELECT FullName,Abbreviation,RegionType, dbo.usp_FormatedDateString (Admitted) as Admitted, Capital FROM StateRegion

It will display correct and store correctly.

Lamak · Accepted Answer · 2014-11-21 19:41:37Z

-2

You can try to use an explicit format for the conversion. You are not explaining why you can't change the format, but I imagine that you are reading the values somehow that are already stored as that. You can use CONVERT:

DECLARE @Admitted VARCHAR(11);
SET @Admitted = '1819-Dec-14'


SELECT CONVERT(DATETIME,RIGHT(@Admitted,2)+' '+
                        SUBSTRING(@Admitted,6,3)+' '+
                        LEFT(@Admitted,4),106);

answered Nov 21, 2014 at 19:41

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Comments

vijaymmali · Accepted Answer · 2014-11-21 20:11:34Z

-3

Chang datatype of date to VARCHAR

answered Nov 21, 2014 at 20:11

vijaymmali

212 bronze badges

Collectives™ on Stack Overflow

Inserting date in sql server

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Related