CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Mehdi Souregi
Mehdi Souregi

Reputation: 3265

Inserting date in sql server

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
);

Upvotes: 1

Views: 13575

Answers (5)

vijaymmali
vijaymmali

Reputation: 21

Chang datatype of date to VARCHAR

Upvotes: -3

Martin Smith
Martin Smith

Reputation: 452947

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.

Upvotes: 6

Stephen Bodine
Stephen Bodine

Reputation: 519

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.

Upvotes: 0

Mitch
Mitch

Reputation: 22251

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

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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);

Upvotes: -2

Related Questions