locked
Cannot insert datetime value in datetime column RRS feed

  • Question

  • Hi,

    I'm having a wierd problem. Tring to add datetime data to a simple table gives me the following error :

    Conversion failed when converting datetime from character string.

    My setup :
    Microsoft SQL Server Express Edition 9.0.3042
    Server collation : Latin1_General_CI_AS
    DB colation : Latin1_General_CI_AS
    Microsoft SQL Server Management Studio Express : 9.00.2047.00
    Microsoft Data Access Components (MDAC) : 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)

    Here's the table definition :

    CREATE TABLE Semaines (
        SemaineOID int identity(1,1)  NOT NULL,
        DateLundi datetime NOT NULL
    )
    ;

    Here's the tests I've tried :

    INSERT INTO Semaines (DateLundi) VALUES ('20080101')
    INSERT INTO Semaines (DateLundi) VALUES ('20080101 00:00:00')
    INSERT INTO Semaines (DateLundi) VALUES ('01/01/2008')
    INSERT INTO Semaines (DateLundi) VALUES (CAST('01/01/2008' as varchar))
    INSERT INTO Semaines (DateLundi) VALUES (CONVERT(datetime, '01/01/2008', 101))

    Tried in SQL SERVER MANAGEMENT STUDIO EXPRESS and also from command line :
    C:\Documents and Settings\Administrator>sqlcmd -S localhost\SQLEXPRESS -d "dbtest" -Q "INSERT INTO Semaines (DateLundi) VALUES ('20080101')"

    Also tried from management studio with "open table" and by typing directly in the cell : Same error message.

    Any clue?
    Wednesday, June 11, 2008 9:39 PM

Answers

  • The error was :
    Conversion failed when converting datetime from character string

    But I found the problem. I didn't see that the table had a check constraint on the date to see if it was a monday :

    USE [agenco]
    GO
    ALTER TABLE [dbo].[Semaines]  WITH CHECK ADD  CONSTRAINT [CHECK_Semaines_DateLundi_EstLundi] CHECK  ((datepart(weekday,[DateLundi])=datepart(weekday,'3 janvier 2000')))
    GO
    ALTER TABLE [dbo].[Semaines] CHECK CONSTRAINT [CHECK_Semaines_DateLundi_EstLundi]

    and the format of the date (3 janvier 2000) was in french, so I changed it to the iso format (20000103) and now everything works.

    I would have liked the error message to specify that the error came from a constraint, so I wouldn't have lost 3 hours debugging this issue ... Still I learned to check the constraints ...

    Thanks anyway everybody.
    Thursday, June 12, 2008 3:10 PM

All replies

  • Hi,

     

    I've tried the script you have given and its working fine. Can you post the error you are getting??

    Thursday, June 12, 2008 2:54 AM
  • Hello,

     

    in Latin1_General_CI_AS, the datetime format is "day/month/year hour:minute/seconde"

    so it's not the good format for the two first statements ( i think )

    20080101 is a japanese date format ( i've used to call the database date format as in older times it was the only format which permitted to have a direct sort 25 years ago with network databases.. )

     

    I've struggled with this problem just 2 weeks ago and i found that error after 6 tests hours ( that's fresh in my memory )

     

    Moreover, you are trying to insert a string into a datetime, it's only working when the string has the good format for the language of your database ( it's why i'm always using cast or convert )

     

    Have a nice day

     

    Thursday, June 12, 2008 8:50 AM
  • The error was :
    Conversion failed when converting datetime from character string

    But I found the problem. I didn't see that the table had a check constraint on the date to see if it was a monday :

    USE [agenco]
    GO
    ALTER TABLE [dbo].[Semaines]  WITH CHECK ADD  CONSTRAINT [CHECK_Semaines_DateLundi_EstLundi] CHECK  ((datepart(weekday,[DateLundi])=datepart(weekday,'3 janvier 2000')))
    GO
    ALTER TABLE [dbo].[Semaines] CHECK CONSTRAINT [CHECK_Semaines_DateLundi_EstLundi]

    and the format of the date (3 janvier 2000) was in french, so I changed it to the iso format (20000103) and now everything works.

    I would have liked the error message to specify that the error came from a constraint, so I wouldn't have lost 3 hours debugging this issue ... Still I learned to check the constraints ...

    Thanks anyway everybody.
    Thursday, June 12, 2008 3:10 PM