t-sql 2012 convert and cast issue

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  February 11, 2016 at 4:02 pm

  #315826

  I have the following t-sql 2012, which is the following:

  DECLARE @endYear varchar(04) = 2016

  select convert(smalldatetime, ('07/31/' + convert(char(02), @endYear - 1)))

  select convert(smalldatetime, '08/01/' + @endYear )

  that is having a conversion error.

  I want one select statement to be 07/31/2015 and the other date to be

  08/01/2016 in a smalldatetime format.

  Thus can you show me the sql to accomplish my goal?

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  February 11, 2016 at 4:36 pm

  #1858214

  It's because of the CHAR(2).

  SQL Server has to convert @EndYear to an integer to do the math, and then convert the result to CHAR(2) as you request. As pointed out at https://technet.microsoft.com/en-us/library/ms191530(v=sql.105).aspx, when you try to convert an integer to a string type that is too small to fit the full integer (as is the case here, trying to put a 4 digit integer into a two character string), an asterisk is returned.

  The resulting string '7/31/*' is not a string that can be converted to smalldatetime.

  You could just switch the CHAR(2) to a CHAR(4) and the commands would work.

  If you are on 2012, I would probably prefer declaring the variable as an integer and using DATEFROMPARTS instead for readability, but this should at least get you around your immediate problem.

  Cheers!

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  February 11, 2016 at 7:22 pm

  #1858228

  wendy elizabeth (2/11/2016)

  ---

  I have the following t-sql 2012, which is the following:

  DECLARE @endYear varchar(04) = 2016

  select convert(smalldatetime, ('07/31/' + convert(char(02), @endYear - 1)))

  select convert(smalldatetime, '08/01/' + @endYear )

  that is having a conversion error.

  I want one select statement to be 07/31/2015 and the other date to be

  08/01/2016 in a smalldatetime format.

  Thus can you show me the sql to accomplish my goal?

  Always check what are you actually trying to convert:

  select '07/31/' + convert(char(02), @endYear - 1)

  And it's desirable to use style for datetime conversions.

  _____________
  Code for TallyGenerator

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  February 11, 2016 at 9:12 pm

  #1858235

  wendy elizabeth (2/11/2016)

  ---

  I have the following t-sql 2012, which is the following:

  DECLARE @endYear varchar(04) = 2016

  select convert(smalldatetime, ('07/31/' + convert(char(02), @endYear - 1)))

  select convert(smalldatetime, '08/01/' + @endYear )

  that is having a conversion error.

  I want one select statement to be 07/31/2015 and the other date to be

  08/01/2016 in a smalldatetime format.

  Thus can you show me the sql to accomplish my goal?

  DECLARE @endYear VARCHAR(4) = '2016';

  SELECT CAST(DATEFROMPARTS(CAST(@EndYear AS INT)-1, 7, 31) AS SMALLDATETIME),

  CAST(DATEFROMPARTS(CAST(@EndYear AS INT), 8, 1) AS SMALLDATETIME);

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato