SQL Server – Can’t change datetime to date

I have this sql which has some date format issue, problem is that I can freely change EndDate to whatever I want, but for StartDate I can’t seem to change it one bit, it always have this timestamp that I am trying to get rid of.

Basically whatever I do in varchar, date or any format I just can’t change it and remove hours minutes and seconds from it.

So I am guessing it is a problem in this else, maybe those two selects aren’t meant to be coupled like this, causing the problem

CASE 
        when p.pname in ('Compz') 
        THEN ji.MADE
        else
        (select min(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('Here','There')) end as StartDate,
    (select max(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('1','2','3', '4')) as EndDate,
             re.pname as resolution
FROM project p

StartDate: 2018-02-22 00:00:00
EndDate: 2018-07-05

Answer

Seems like the issue here is, You’re converting the Subquery result to Date, however the return of the first column in your case (ji.MADE) is still datetime. So by default, SQL Server will cast the other result sets also to DATETIME.

there are 2 approaches you can use here, the first one is to cast ji.MADE also as DATE

CASE 
    when p.pname in ('Compz') 
        THEN CONVERT(DATE,ji.MADE)-- Like This
    else
        (select min(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('Here','There')) end as StartDate,
    (select max(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('1','2','3', '4')) as EndDate,
             re.pname as resolution
FROM project p

or put a cast all over the CASE and convert it as DATE

CONVERT(DATE,
CASE 
    when p.pname in ('Compz') 
        THEN ji.MADE
    else
        (select min(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('Here','There')) end) as StartDate,
    (select max(CONVERT(date, MADE)) from changegroup cg1 inner join changeitem ci1 on ci1.groupid = cg1.id
             where ji.id = cg1.issueid and CONVERT(nvarchar, ci1.NSTRING) in ('1','2','3', '4')) as EndDate,
             re.pname as resolution
FROM project p