Post reply

Date conversion problem

  • September 14, 2012 at 10:53 am

    #261671

    I am trying to convert varchar data '27/04/2012'( dd/mm/yyyy) to date column as like below conversion

    cast (Processing_Date as date).

    But it giving error like 'Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string'

    Seems to be varchar data is changing into yyyy/mm/dd(2012/27/04) after conversion.thats is the problem. i.e month and day value changed.

    Please help to resolve the issue by correct format before loading data?

    Any help will be appreciated!!!!!!!!!

    Regards,
    Kumar

  • September 14, 2012 at 10:59 am

    #1538037

    KumarSQLDBA (9/14/2012)

    I am trying to convert varchar data '27/04/2012'( dd/mm/yyyy) to date column as like below conversion

    cast (Processing_Date as date).

    But it giving error like 'Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting date and/or time from character string'

    Seems to be varchar data is changing into yyyy/mm/dd(2012/27/04) after conversion.thats is the problem. i.e month and day value changed.

    Please help to resolve the issue by correct format before loading data?

    Any help will be appreciated!!!!!!!!!

    The default string format for converting a character string to date is YYYY-MM-DD. try this:

    select convert(date, Processing_Date, 103)

  • September 14, 2012 at 12:08 pm

    #1538061

    Read this link for reasons why Lynn chose 103 and what all those numbers mean:

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

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • September 14, 2012 at 12:19 pm

    #1538065

    SET DATEFORMAT DMY;

    --SET DATEFORMAT MDY

    DECLARE @processing_date AS varchar( 10 );

    SET @processing_date = '27/04/2012';

    SELECT CAST( @Processing_Date AS date );

    this should work as well, but I think it is better to use previous solution and understand the options available

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • September 14, 2012 at 3:46 pm

    #1538196

    Better still, just always use 'YYYYMMDD[ 24h:mm:ss.sss]', which always works, regardless of SQL settings.

    '20120427'

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • September 14, 2012 at 7:48 pm

    #1538240

    ScottPletcher (9/14/2012)

    Better still, just always use 'YYYYMMDD[ 24h:mm:ss.sss]', which always works, regardless of SQL settings.

    '20120427'

    Problem is you don't always have that choice.

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

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