Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    10

    Unanswered: Conversion problems between mssql and access

    Hello

    When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :

    Server: Msg 257, Level 16, State 3, Line 1
    Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.


    I dont understand why it try to insert it as a float?!

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Because from a SQL Server point of view DateTime values ARE float values.

    So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"

    Look in Books On Line for better help on conversions.
    Davide Mauri
    http://www.davidemauri.it

  3. #3
    Join Date
    Mar 2003
    Posts
    10
    Hello

    I have tried CONVERT. But I dont know to which datatype I should convert my source value.
    I have converted to varchar and nvarchar but I still got the same error.

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    can you post your code, and specify from what kind of data type you what convert to?
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Mar 2003
    Posts
    10
    INSERT INTO LINKEDACCESS...ContactTarget
    Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
    from tblContactTarget
    WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
    WHERE CT.Target = tblContactTarget.TargetCode)


    ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
    This INSERT will trigger the error I wrote about.

  6. #6
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have
    Davide Mauri
    http://www.davidemauri.it

Posting Permissions

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