Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

Why would a select of an integer return a datetime value in a CASE statement?

Why doesn't this statement return 1? Instead it returns 1900-01-02 00:00:00.000. How would you format this to  return a 1 or 2 in the last 2 conditions and a proper datetime value in the other conditions of the CASE?

SELECT
CASE WHEN ''='Day' THEN '01/01/2001'
WHEN ''='Week' THEN DATEADD(d, 1 - DATEPART(dw, '01/01/2001'), '01/01/2001')  
WHEN ''='Month' THEN Month('01/01/2001') + Year('01/01/2001')
WHEN ''='Year' THEN Year('01/01/2001')
WHEN ''='Employee' THEN 2
ELSE 1 END as [grouping]
0
pauldes
Asked:
pauldes
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Since you are returning a date as part of the second WHEN ('WHEN ''= 'Week' .....  ) ,query optimiser asssumes that , the query has to return a date. So you need to cast that date as varchar

SELECT
CASE WHEN ''='Day' THEN '01/01/2001'
WHEN ''='Week' THEN CONVERT(VARCHAR(10),DATEADD(d, 1 - DATEPART(dw, '01/01/2001'), '01/01/2001') ,103)
WHEN ''='Month' THEN Month('01/01/2001') + Year('01/01/2001')
WHEN ''='Year' THEN Year('01/01/2001')
WHEN ''='Employee' THEN 2
ELSE 1 END as [grouping]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now