How can I convert char field to be used for calculation?

• mbatchelor

  SSC Veteran

  Points: 235

  More actions

  January 29, 2016 at 12:43 pm

  #99019

  I am trying to use a char field in a calculation with no success. Here is my sql:

  SELECT REG_CONFIG.CUR_YR_DFLT

  ,SECTION_MASTER.YR_CDE

  ,CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1,0) AS int) AS NEWYEAR

  ,CONVERT(INT,SECTION_MASTER.YR_CDE) AS YR_CDE_1

  ,CONVERT(INT,REG_CONFIG.CUR_YR_DFLT) AS REG_CONF_YR

  ,CONVERT(INT,REG_CONFIG.CUR_YR_DFLT) - 1

  ,SECTION_MASTER.CRS_CDE AS 'INTEGRATION_ID'

  ,SECTION_MASTER.CRS_TITLE AS 'COURSE_SECTION_NAME'

  ,SECTION_MASTER.CRS_CDE AS 'COURSE_SECTION_ID'

  ,LEFT(CONVERT(NVARCHAR(30),SECTION_MASTER.FIRST_BEGIN_DTE,120),10) AS 'START_DT'

  ,LEFT(CONVERT(NVARCHAR(30),SECTION_MASTER.LAST_END_DTE,120),10) AS 'END_DT'

  ,SECTION_MASTER.YR_CDE + SECTION_MASTER.TRM_CDE AS 'TERM_ID'

  ,' ' AS 'COURSE_INTEGRATION_ID'

  ,' ' AS 'COURSE_SECTION_SECTION_DELIVERY'

  ,SECTION_MASTER.CRS_CAPACITY AS 'MAXIMUM_ENROLLMENT_COUNT'

  ,SECTION_MASTER.CREDIT_HRS AS 'CREDIT_HOURS'

  ,SECTION_MASTER.REQUEST_NUM AS 'REGISTRATION_CALL_NUMBER'

  ,SECTION_MASTER.SESS_CDE AS 'SESSION CODE'

  ,LEFT(CONVERT(NVARCHAR(30),GETDATE(),120),10)

  FROM SECTION_MASTER,REG_CONFIG

  WHERE SECTION_MASTER.CRS_CANCEL_FLG <> 'N'

  AND (CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 2,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 3,0) AS int))

  I am only getting the current CUR_YR data. I need to extract seven years of data. I have tried CAST and CONVERT and I do not get the results that I need. We are on Sql Server management Studio 2012. Can someone help me.

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  January 29, 2016 at 12:53 pm

  #1855363

  reformatted code:

  SELECT REG_CONFIG.CUR_YR_DFLT,

  SECTION_MASTER.YR_CDE,

  CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1, 0) AS INT) AS NEWYEAR,

  CONVERT(INT, SECTION_MASTER.YR_CDE) AS YR_CDE_1,

  CONVERT(INT, REG_CONFIG.CUR_YR_DFLT) AS REG_CONF_YR,

  CONVERT(INT, REG_CONFIG.CUR_YR_DFLT) - 1,

  SECTION_MASTER.CRS_CDE AS 'INTEGRATION_ID',

  SECTION_MASTER.CRS_TITLE AS 'COURSE_SECTION_NAME',

  SECTION_MASTER.CRS_CDE AS 'COURSE_SECTION_ID',

  LEFT(CONVERT(NVARCHAR(30), SECTION_MASTER.FIRST_BEGIN_DTE, 120), 10) AS 'START_DT',

  LEFT(CONVERT(NVARCHAR(30), SECTION_MASTER.LAST_END_DTE, 120), 10) AS 'END_DT',

  SECTION_MASTER.YR_CDE + SECTION_MASTER.TRM_CDE AS 'TERM_ID',

  ' ' AS 'COURSE_INTEGRATION_ID',

  ' ' AS 'COURSE_SECTION_SECTION_DELIVERY',

  SECTION_MASTER.CRS_CAPACITY AS 'MAXIMUM_ENROLLMENT_COUNT',

  SECTION_MASTER.CREDIT_HRS AS 'CREDIT_HOURS',

  SECTION_MASTER.REQUEST_NUM AS 'REGISTRATION_CALL_NUMBER',

  SECTION_MASTER.SESS_CDE AS 'SESSION CODE',

  LEFT(CONVERT(NVARCHAR(30), GETDATE(), 120), 10)

  FROM SECTION_MASTER,

  REG_CONFIG

  WHERE SECTION_MASTER.CRS_CANCEL_FLG <> 'N'

  AND (

  CONVERT(INT, SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT, 0) AS INT)

  OR CONVERT(INT, SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1, 0) AS INT)

  OR CONVERT(INT, SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 2, 0) AS INT)

  OR CONVERT(INT, SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 3, 0) AS INT)

  );

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

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  January 29, 2016 at 12:54 pm

  #1855365

  mbatchelor (1/29/2016)

  ---

  I am trying to use a char field in a calculation with no success. Here is my sql:

  SELECT REG_CONFIG.CUR_YR_DFLT

  ,SECTION_MASTER.YR_CDE

  ,CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1,0) AS int) AS NEWYEAR

  ,CONVERT(INT,SECTION_MASTER.YR_CDE) AS YR_CDE_1

  ,CONVERT(INT,REG_CONFIG.CUR_YR_DFLT) AS REG_CONF_YR

  ,CONVERT(INT,REG_CONFIG.CUR_YR_DFLT) - 1

  ,SECTION_MASTER.CRS_CDE AS 'INTEGRATION_ID'

  ,SECTION_MASTER.CRS_TITLE AS 'COURSE_SECTION_NAME'

  ,SECTION_MASTER.CRS_CDE AS 'COURSE_SECTION_ID'

  ,LEFT(CONVERT(NVARCHAR(30),SECTION_MASTER.FIRST_BEGIN_DTE,120),10) AS 'START_DT'

  ,LEFT(CONVERT(NVARCHAR(30),SECTION_MASTER.LAST_END_DTE,120),10) AS 'END_DT'

  ,SECTION_MASTER.YR_CDE + SECTION_MASTER.TRM_CDE AS 'TERM_ID'

  ,' ' AS 'COURSE_INTEGRATION_ID'

  ,' ' AS 'COURSE_SECTION_SECTION_DELIVERY'

  ,SECTION_MASTER.CRS_CAPACITY AS 'MAXIMUM_ENROLLMENT_COUNT'

  ,SECTION_MASTER.CREDIT_HRS AS 'CREDIT_HOURS'

  ,SECTION_MASTER.REQUEST_NUM AS 'REGISTRATION_CALL_NUMBER'

  ,SECTION_MASTER.SESS_CDE AS 'SESSION CODE'

  ,LEFT(CONVERT(NVARCHAR(30),GETDATE(),120),10)

  FROM SECTION_MASTER,REG_CONFIG

  WHERE SECTION_MASTER.CRS_CANCEL_FLG <> 'N'

  AND (CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 1,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 2,0) AS int)

  OR CONVERT(INT,SECTION_MASTER.YR_CDE) = CAST(ROUND(REG_CONFIG.CUR_YR_DFLT - 3,0) AS int))

  I am only getting the current CUR_YR data. I need to extract seven years of data. I have tried CAST and CONVERT and I do not get the results that I need. We are on Sql Server management Studio 2012. Can someone help me.

  Please post DDL and sample data (as insert statements) to be sure what's the problem. To be sure on how we need the data, read the article in my signature.

  It's usually a bad practice to convert the data type of a column in the where or join clauses. It's also recommended to change the JOIN syntax to SQL-92 version.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  January 29, 2016 at 12:55 pm

  #1855366

  mbatchelor (1/29/2016)

  ---

  I am trying to use a char field in a calculation with no success.

  .

  .

  .

  I am only getting the current CUR_YR data. I need to extract seven years of data. I have tried CAST and CONVERT and I do not get the results that I need. We are on Sql Server management Studio 2012. Can someone help me.

  Which char column are you referring to?

  Also, can you please post the definitions for the involved tables including the indexes and constraints?

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

• mbatchelor

  SSC Veteran

  Points: 235

  More actions

  January 29, 2016 at 2:04 pm

  #1855395

  I am trying to use the SECTION_MASTER.YR_CDE (char(4)) to compare to REG_CONFIG.CUR_YR_DFLT (char(4)) minus 1. Then doing it for past year 2 and 3 and etc. back for 6 years.

• mbatchelor

  SSC Veteran

  Points: 235

  More actions

  January 29, 2016 at 2:27 pm

  #1855399

  I would like to express my thanks to all. The answer is that there was another criteria where the class status was incorrectly being checked. I do not know why all prior classes have a cancel status in them but they do. the coding that I have posted works just beautifully with the exception of the check for class status. Problem is solved.