Sample statement to demonstrate the use of CAST and CASE functions in SQL

 

General Syntax: CAST(CASE RecName.FieldName When Condition Then Statement Else Condition End AS Datatye)

 

Sample Statement:

 

CAST(CASE A.HR_STATUS WHEN 'A' THEN DATEDIFF(MONTH

 ,A.LAST_HIRE_DT

 ,GETDATE()) ELSE DATEDIFF(MONTH

 ,A.LAST_HIRE_DT

 ,A.TERMINATION_DT) END/12.00 AS DECIMAL(5,2)) AS 'UST_EXP_YRS'

Here the statement is used to cast the datedifference in years as decimal according to the HR_STATUS field value.