SQL IF ELSE Statement During SELECT?

Question

Need help... the following query gives an error when trying to use IF Else. The SERIAL_NUMBER field has the two digit year in it and I want to extract it and display it as a 4 digit year in it's own field.

SELECT 
   [START_TIMESTAMP]
  ,[END_TIMESTAMP]
  ,[SERIAL_NUMBER]
  ,[MODEL]
  ,[TESTING_ACTION_RESULT]
  ,[FAILED_STEP]
  ,[DISPOSITION]
  ,[TesterId]
  ,[Location]
  ,[Customer]
  ,IF CAST(substring([SERIAL_NUMBER],3,2)AS INT)>90
    '19'+substring([SERIAL_NUMBER],3,2) AS MFG_YYYY
   ELSE
    '20'+substring([SERIAL_NUMBER],3,2) AS MFG_YYYY
FROM [DATABASE_1].[dbo].[TBL_Unit_Test]
GO

Taryn · Accepted Answer · 2013-10-04 21:10:38Z

1

You cannot use an IF in a SELECT statement, instead you will use a CASE expression:

SELECT 
   [START_TIMESTAMP]
  ,[END_TIMESTAMP]
  ,[SERIAL_NUMBER]
  ,[MODEL]
  ,[TESTING_ACTION_RESULT]
  ,[FAILED_STEP]
  ,[DISPOSITION]
  ,[TesterId]
  ,[Location]
  ,[Customer]
  ,case 
    when CAST(substring([SERIAL_NUMBER],3,2)AS INT)>90
    then '19'+substring([SERIAL_NUMBER],3,2)
    else '20'+substring([SERIAL_NUMBER],3,2) end AS MFG_YYYY
FROM [DATABASE_1].[dbo].[TBL_Unit_Test]

answered Oct 4, 2013 at 21:10

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Satpal · Accepted Answer · 2013-10-04 21:12:05Z

0

You can use CASE expression, like

SELECT 
   [START_TIMESTAMP]
  ,[END_TIMESTAMP]
  ,[SERIAL_NUMBER]
  ,[MODEL]
  ,[TESTING_ACTION_RESULT]
  ,[FAILED_STEP]
  ,[DISPOSITION]
  ,[TesterId]
  ,[Location]
  ,[Customer]
  , MFG_YYYY =
   CASE 
    WHEN CAST(substring([SERIAL_NUMBER],3,2)AS INT)>90 
       THEN '19'+substring([SERIAL_NUMBER],3,2)
    ELSE '20'+substring([SERIAL_NUMBER],3,2)
   END
FROM [DATABASE_1].[dbo].[TBL_Unit_Test]
GO

answered Oct 4, 2013 at 21:12

Satpal

134k13 gold badges168 silver badges171 bronze badges

Collectives™ on Stack Overflow

SQL IF ELSE Statement During SELECT?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related