Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: error : nvarchar into smalldatetime

    Hi !

    I am having a problem with MS SQL 2005
    for a column
    [DateInsert] [smalldatetime] NOT NULL

    and that Query

    INSERT INTO Users (DateInsert) VALUES (CONVERT(DATETIME,'2007-12-17',120))

    I am getting a conversion error : nvarchar into smalldatetime

    it seems that I am getting this error only with days > 12

    2007-12-17 does not work but 2007-12-01 works


    any idea ?
    thank you

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    The reason is quite simple, The date format is in the form
    yyyy-dd-mm. so if u use 2007-12-17 will throw an error instead you give as 2007-17-12 will work

  3. #3
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink Ring a bell

    hmmmm seems your not awake yet are you.

    if your getting the error with the daydate's bigger then 12
    then what you see as MONTH = your DAY and DAY =MONTH.

    Month (jan - Dec) = 12
    Day 1 to 31 (some times 30 and once 28 or 29)

    see your regionalsettings

    AND get some coffee to WAKE UP ;-)
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  4. #4
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    U said that u r getting the error if the datetime is 2007-12-17 and it is working fine if it is 2007-12-01, which seems that 2007-12-01 format is like yyyy-dd-mm so it worked fine. you mentioned that "it seems that I am getting this error only with days > 12" so i felt that the problem will be due to the wrong format.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depending on settings, SQL Server will not recognise dates in this format. AFAIK any and all installations of SQL Server will recognise YYYYMMDD (i.e. minus the dashes). This is the ISO standard format and something that most commercial RDBMSs comply to.

  6. #6
    Join Date
    Dec 2005
    Posts
    266
    but there is no reason to use yyyy-dd-mm as far as yyyy-mm-dd is the only universal datetime
    2007-12-17 should work fine with CONVERT(DATETIME,'2007-12-17',120)

    pootle flump I must use 20071217 in sted of 2007-12-17 ???
    on my computer ad on 3 or 4 customers it is working fine only on 2 customer's computer it does not ?

    those dates are really crazy !
    Last edited by anselme; 01-21-08 at 08:48.

  7. #7
    Join Date
    Jul 2007
    Posts
    96
    Quote Originally Posted by pootle flump
    Depending on settings, SQL Server will not recognise dates in this format. AFAIK any and all installations of SQL Server will recognise YYYYMMDD (i.e. minus the dashes). This is the ISO standard format and something that most commercial RDBMSs comply to.
    Using YYYYMMDD should be the safest. More information on SQL Server's DateTime available here: http://www.sql-server-performance.co...tatype_p1.aspx

  8. #8
    Join Date
    Dec 2005
    Posts
    266
    thanks Diabolic I'll try it
    and for date time is it yyyyMMdd hhmmss ?

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by anselme
    but there is no reason to use yyyy-dd-mm as far as yyyy-mm-dd is the only universal time
    then 2007-12-17 should work fin with CONVERT(DATETIME,'2007-12-17',120)

    pootle flump I must use 20071217 in sted of 2007-12-17 ???
    on my computer ad on 3 or 4 customers it is working fine only on 2 customers it does not ?

    those dates are really crazy !
    If you check profiler I bet you will find that there are some set statements being passed in the background. Again this is why YYYYMMDD is preferred as it is recognised as independent of any other setting. Of course, if you are using a client side tool then the absolutley best method is to use a date data type and don't pass a string. It depends what your customers are using. There is also no need to convert - SQL Server will implicitely comvert i.e.
    Code:
    INSERT INTO Users (DateInsert) VALUES ('20071217')

  10. #10
    Join Date
    Dec 2005
    Posts
    266
    thanks a lot ! i'll use this method from now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •