none
Getting Data Type Conversersion Error with SmallDateTime Data Type RRS feed

  • Question

  • I have a SQL statement that is returning the error:

    Conversion failed when converting character string to smalldatetime data type.

    The T-SQL statement is:

    update

    [Staging].[TweetreachPostsStage_1] setpublished_date =dateadd(hour,-8,(cast(common.dbo.ParseColumn(convert_date,'UTC',1)assmalldatetime)))


     

    wherepublished_date isnull

    The data type are as follows:

    'published_date' is datetime

    'convert_date' is nvarchar(255)

    SQL server is 2012. The database and SSIS ETL was copied from one SQL Server 2012 to another SQL Server 2012. This works one server but not the other.

    I tried changing the smalldatetime to other date/time data types and I still get the same errors.

    Thursday, July 6, 2017 4:05 PM

All replies

  • You can use try_cast but you need to find the problem data.

    update

    [Staging].[TweetreachPostsStage_1] 
    set published_date =dateadd(hour,-8,(try_cast(common.dbo.ParseColumn(convert_date,'UTC',1) as smalldatetime)))

    • Proposed as answer by DeviantLogic Thursday, July 6, 2017 7:14 PM
    Thursday, July 6, 2017 4:20 PM
    Moderator
  • You can probably find the rows that are causing your error by running

    Select *, common.dbo.ParseColumn(convert_date,'UTC',1) As ParsedData
    From [Staging].[TweetreachPostsStage_1]
    Where convert_date Is Not Null
      And try_cast(common.dbo.ParseColumn(convert_date,'UTC',1) as smalldatetime) Is Null
    And published_date is null

    Your problem may be caused by differences in the settings on your server.  For example, if the first server is using language us_english and the second server is using language british, then '06302017' is a valid datetime on the first server, but not on the second server.

    Tom

    Thursday, July 6, 2017 9:35 PM
  • Storing date/time in varchar is an utterly bad idea. You will get bad data sooner or alter.

    Use this to find the bad dates, or where the function fails to produce somethiing useful:

    SELECT convert_date, common.dbo.ParseColumn(convert_date,'UTC',1), * FROM  [Staging].[TweetreachPostsStage_1]
    WHERE try_cast(common.dbo.ParseColumn(convert_date,'UTC',1) as smalldatetime) IS NULL

    Thursday, July 6, 2017 9:45 PM
  • I found another solution that fixed my problem.
    Thursday, July 6, 2017 10:54 PM
  • Hi TWIKLE,

    I'm glad that you have resolved your issue. Could you please kindly share us your solution? So that it will help others with the relevant issue find the answer more easily.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 7, 2017 7:31 AM