Datetime conversion

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  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)

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• jphillips 46546

  SSC-Addicted

  Points: 439

  More actions

  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