Post reply

Sql Server 2008: Conversion From Text data type to Datetime

  • December 11, 2012 at 4:25 am

    #267906

    Hi All,

    How do I convert a text data type column to datetime?

    The query below returns this error: Explicit conversion from data type text to datetime is not allowed.

    SELECT CONVERT(DATETIME, vchValue) FROM dtlConfiguration WHERE vchParameter = 'LastRTADImport'

    The data in 'vchValue' column is in this format: 'Jul 21 2010 10:15AM'

    Regards

    Teee

  • December 11, 2012 at 4:28 am

    #1567361

    Convert to VARCHAR then convert to datetime.

    CONVERT(DATETIME,CONVERT(VARCHAR,vchValue))

    The following link details what can and cannot be converted

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

  • December 11, 2012 at 4:29 am

    #1567362

    Are you sure that is causing the problem?

    This should work

    select convert(datetime, 'Jul 21 2010 10:15AM')

    Could be due to your settings maybe.


    Cursors never.
    DTS - only when needed and never to control.

  • December 11, 2012 at 4:31 am

    #1567364

    nigelrivett (12/11/2012)

    Are you sure that is causing the problem?

    This should work

    select convert(datetime, 'Jul 21 2010 10:15AM')

    Could be due to your settings maybe.

    The question and topic title say they are converting from TEXT to DATETIME, you cannot convert TEXT directly to DATETIME, you have to do an implicit conversion to VARCHAR then to DATETIME

  • December 11, 2012 at 4:39 am

    #1567369

    Thanks for the link and the example posted above works perfectly. 🙂

  • December 11, 2012 at 4:41 am

    #1567370

    Teee (12/11/2012)

    Thanks for the link and the example posted above works perfectly. 🙂

    Happy to help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply