Using a Date compariason in a Case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using a Date compariason in a Case statement

Author

Topic

Nokose
Starting Member

1 Post

Posted - 2015-03-19 : 13:04:43

Good Day,
I am trying to use a date comparison in a statement using the year statement as well. Here is what I have:

Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'
When Month([LastHireDate]) = '5' then '8'
When Month([LastHireDate]) = '6' then '7'
When Month([LastHireDate]) = '7' then '6'
When Month([LastHireDate]) = '8' then '5'
When Month([LastHireDate]) = '9' then '4'
When Month([LastHireDate]) = '10' then '3'
When Month([LastHireDate]) = '11' then '2'
When Month([LastHireDate]) = '12' then '1'
End As LastHireDate,

When I am looking at it [LastHireDate] is showing that red line underneath. The < symbol has a red line and @EndYearlyDate has a red line. I can not seem to get them to clear and am, wondering what I am missing. Any Help is appreciated.

Here is the full piece that the Case resides in:

Insert _Test
SELECT
EmpNo,
PersonIdNo,
REPLACE(PersonTaxIdNo,'-',''),
LastName,
FirstName,
Case [EmploymentStatus]
When 'RFT' then 'Yes'
When 'RPT' then 'Yes'
When 'PD' then 'No'
When 'TEM' then 'No'
End As EmploymentStatus,
BirthDate,
SeniorityDate,
0,
'No',
0,
Case [LastHireDate]
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
When Month([LastHireDate]) = '1' then '12'
When Month([LastHireDate]) = '2' then '11'
When Month([LastHireDate]) = '3' then '10'
When Month([LastHireDate]) = '4' then '9'
When Month([LastHireDate]) = '5' then '8'
When Month([LastHireDate]) = '6' then '7'
When Month([LastHireDate]) = '7' then '6'
When Month([LastHireDate]) = '8' then '5'
When Month([LastHireDate]) = '9' then '4'
When Month([LastHireDate]) = '10' then '3'
When Month([LastHireDate]) = '11' then '2'
When Month([LastHireDate]) = '12' then '1'
End As LastHireDate,
0
FROM EmployeePay_Job_Curr
Where EmploymentStatus Not in ('VOL', 'CON') and
EmployeeStatus Not in ('Not Employee') and
(TerminationDate >= @StartYearlyDate or TerminationDate is Null) and
SeniorityDate <= @PPStart and
EmploymentStatusOrgCode = 'ABC Corp'

Thank you.

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 14:31:13

Syntax is


CASE MyColumn 
   WHEN 1 THEN ...
   WHEN 2 THEN ...


CASE WHEN MyColumn = 1 THEN ...
   WHEN MyColumn = 2 THEN ...

you are using half-and-half

and you need to change yours to use the second style

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 14:31:57

CASE has two modes:

1. CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...

2. CASE
WHEN expression1 = value1 THEN result1
...

It appears that you are trying a sort of hybrid approach which is not supported.

Kristen
Test

22859 Posts

Posted - 2015-03-19 : 14:33:40

P.S. I haven't checked it, but this shorter form might work:


Case 
When YEAR([LastHireDate]) < Year(@EndYearlyDate) then 12
ELSE 13 - Month([LastHireDate])
End As LastHireDate,

the datatype will be integer, whereas yours was String, dunno if that is important though?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 14:33:42

However why not simplify:

CASE WHEN When YEAR([LastHireDate]) < Year(@EndYearlyDate) then '12'
ELSE right('0'+cast(13-MONTH('2015-12-12') as varchar(2)),2)
...

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-19 : 15:13:34

@Kristen -- we should synchronize watches!

Kristen
Test

22859 Posts

Posted - 2015-03-20 : 03:44:46

Hehehe ... I reckon we need one more person to chip in as I was told when training I should:

Tell 'em what you are going to tell 'em
Tell 'em
Tell 'em what you told 'em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources