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

# 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
1 Solution

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

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