Post reply

Datetime conversion

  • April 8, 2015 at 7:27 am

    #302832

    Hi,

    I have simple query, I'm trying to convert a varchar value to datetime format but it throwing below error:

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    here is sample query:

    declare @test-2 varchar(20)

    set @test-2 ='30/04/2015 23:59:59'

    select CONVERT(datetime, @test-2)

    One interesting observation above query run fines in Sql 2008 but not in Sql 2014

    Please advice how to fix the error

    Thanks

    Sam

  • April 8, 2015 at 7:35 am

    #1789532

    Specify the correct format code for the CONVERT, the one that corresponds to the format of datetime you're passing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2015 at 7:36 am

    #1789533

    sam 55243 (4/8/2015)

    Hi,

    I have simple query, I'm trying to convert a varchar value to datetime format but it throwing below error:

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    here is sample query:

    declare @test-2 varchar(20)

    set @test-2 ='30/04/2015 23:59:59'

    select CONVERT(datetime, @test-2)

    One interesting observation above query run fines in Sql 2008 but not in Sql 2014

    Please advice how to fix the error

    Thanks

    Sam

    Use a different format:

    declare @test-2 varchar(20)

    set @test-2 ='2015-04-30T23:59:59'

    select CONVERT(datetime, @test-2)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • April 8, 2015 at 7:46 am

    #1789538

    Here you go

    declare @test-2 varchar(20)

    set @test-2 ='30/04/2015 23:59:59'

    select CONVERT(datetime, @test-2,103)

    James Phillips
    Sr. Consultant
    Pragmatic Works

Viewing 4 posts - 1 through 3 (of 3 total)

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