Using a CASE WHEN in SQL and converting date results to a certain format

I have a report where I am using the following formula:

CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR(10),PEDTEDC.ValueInfo, 112) END AS EDC

I need to convert the results (date field) to print out at 09-18-2014.  I tried using the formula above but this is not working.  
Instead it is printing out as

EDC
20140918

Can someone help?
kvrogersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Randy PooleCommented:
CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL 
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 112) ELSE '' END AS EDC

Open in new window

0
Sytech SolutionsCommented:
Try using 110 instead of 112 in your CONVERT function.

Hope that helps.
0
Randy PooleCommented:
Now that does assume ValueInfo is a Datetime field
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

kvrogersAuthor Commented:
This is just a date field.  She tells me now she wants the value to read mm/dd/yy which should be converted to 101, I think.

so should read:

EDC
09/18/14
0
kvrogersAuthor Commented:
Remember that this is a query result.  I checked and it is listed as a DATE field in the database.  
I am getting results when I run my report, they are just not in the format that I need.

Here is current formula

CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 101) ELSE '' END AS EDC

So here is what I want:

when the query field PEDTEDC (PEDIATRIC ESTIMATED DATE OF CONCEPTION) IS NOT  NULL THEN
Convert the value of that field to the date format of mm/dd/yy and name the column EDC.

When I run this report, I am still getting the same results as listed above:
EDC
20140918
0
PortletPaulfreelancerCommented:
Please refer to: SQL Server Date Styles (formats) using CONVERT()

MM/DD/YY is style number 1
CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 1) ELSE '' END AS EDC

MM/DD/YYYY is style number 101
CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 101) ELSE '' END AS EDC

MM-DD-YYYY from 09-18-2014 (in the question) is style number 110
CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 110) ELSE '' END AS EDC
0
kvrogersAuthor Commented:
How about this:

CASE WHEN MAX(PEDTEDC.ValueInfo) IS NOT NULL
        THEN CONVERT (VARCHAR,MAX(PEDTEDC.ValueInfo), 101) ELSE ' ' END AS EDC
0
PortletPaulfreelancerCommented:
not sure what you are asking for.

originally (in the question) you said you wanted
    need to convert the results (date field) to print out at 09-18-2014.
but you got
    20140918

and you got that result because you use style number 112
----------------------------------------------------------------------------

later you ask for MM/DD/YY

this 2 year style, that commences with MM and uses slashes as delimiter is style number 1
----------------------------------------------------------------------------

In your most recent comment, immediately above, you have altered the case expression
and you are using style number 101

that style provides a 4 digit year MM first with slashes: MM/DD/YYYY
----------------------------------------------------------------------------

are you asking about the date styles or about the case expression?

if there is a problem with the case expression, can you describe what that problem is?
0
PortletPaulfreelancerCommented:
by the way, I don't think you need a case expression for this.

if the value is NULL then it will remain NULL

CONVERT (VARCHAR,MAX(PEDTEDC.ValueInfo), 1)  --<< pick the wanted style number

or, if you must get an empty string

ISNULL(CONVERT (VARCHAR,MAX(PEDTEDC.ValueInfo), 1)  ,'')

or

COALESCE(CONVERT (VARCHAR,MAX(PEDTEDC.ValueInfo), 1)  ,'')
0
kvrogersAuthor Commented:
use the example above to show date as mm/dd/yy
CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 1) ELSE '' END AS EDC

and this was result
EDC
20140918
20140918
20140918
20140918
20140918
20140918
20140918
20140918
20140918
20140918
20140918
20140918
0
PortletPaulfreelancerCommented:
Is PEDTEDC.ValueInfo a date/time or a var/char?

try

CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN CONVERT(VARCHAR,CONVERT(DATETIME,Max(PEDTEDC.ValueInfo), 112),1) ELSE '' END AS EDC
0
kvrogersAuthor Commented:
This is part of a query and when I look up the QUERY field type it is set to DATE

I tried this above and this did not work.

OK,  here goes.  This is the entire report attached.
Report-for-OB.docx
0
PortletPaulfreelancerCommented:
Did you know that "select distinct" is absolutely, fully, completely and entirely redundant when doing a "group by"?

Never include BOTH in the same query

i.e.

select distinct --<< get rid of this distinct
......
group by
......

Some reading:
see: Select Distinct is returning duplicates ...

Why I Hate DISTINCT
0
PortletPaulfreelancerCommented:
Back to the question topic, in the attached query you are still using date style 112

date style 112 is
YYYYMMDD

Search "PEDTEDC.ValueInfo" (5 hits in 1 file)
  new  10 (5 hits)
	Line 208: CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL 
	Line 209:        THEN CONVERT(VARCHAR,CONVERT(DATETIME,Max(PEDTEDC.ValueInfo), 112),1) ELSE '' END AS EDC,
	Line 210: --CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL 
	Line 211: --	 THEN CONVERT(VARCHAR,Max(PEDTEDC.ValueInfo), 101) ELSE '' END AS EDC,
	Line 477: PEDTEDC.ValueInfo,

Open in new window

PEDTEDC is an alias established at line 294:

LEFT JOIN #QUERY PEDTEDC (NOLOCK)
      ON P.SourceID = PEDTEDC.SourceID
      AND P.VisitID = PEDTEDC.VisitID
      AND PEDTEDC.Qry = 'PEDTEDC'
     
So PEDTEDC comes from #QUERY, which is established here:testfdb.dbo.RegAcctQuery_Result.ValueInfo
Exactly what type of data is testfdb.dbo.RegAcctQuery_Result for the column ValueInfo?
0
PortletPaulfreelancerCommented:
You conclude your query with a very long list of fields in a GROUP BY
But many of those fields should NOT be in that GROUP BY because you are using MAX() on most of them.

BUT, there are some references to MAX() that are probably missing (in yellow) and there may be more than just those highlightedMAX() is missing where highlighted
0
kvrogersAuthor Commented:
I made the changes as you suggested above.  Here is my new report (Attached)

When I run the report in TEST I am still getting duplicates.  ADM.RHONDA is a test patient.

Name                      UnitNumber      AccountNumber  
ADM,RHONDA      M00000744      H00000017277
ADM,RHONDA      M00000744      H00000017277

EDC is still incorrect format.
EDC
20140918
20140918
Report-for-OB-2.docx
0
kvrogersAuthor Commented:
I read one of the articles that you suggested to find out WHY the rows are repeating with the same name.  Well that is because of the last three columns.  Their values are different.  So I will have to find out more information about those fields.

Still need the date formula correct .
0
kvrogersAuthor Commented:
When we look at the field in the query, it pulls up a calendar and has you pick a date.  The date then appears in the query as 09/18/2014.  So I decided to take the CONVERT out all together

CASE WHEN PEDTEDC.Qry = 'PEDTEDC' AND Max(PEDTEDC.ValueInfo) IS NOT NULL
       THEN Max(PEDTEDC.ValueInfo) END AS 'EDC',

but get same results.
0
PortletPaulfreelancerCommented:
>>"take the CONVERT out all together"
excellent choice

another method:

MAX( case when PEDTEDC.Qry = 'PEDTEDC'  then PEDTEDC.ValueInfo end ) AS EDC


then if you need to specify a date format, apply this AFTER the aggregate function

CONVERT( varchar, MAX( case when PEDTEDC.Qry = 'PEDTEDC'  then PEDTEDC.ValueInfo end ), 101 ) AS EDC
0
kvrogersAuthor Commented:
When I enter the above, I get this on execution:

(1 row(s) affected)

(43 row(s) affected)

(1 row(s) affected)

(5 row(s) affected)
Msg 8120, Level 16, State 1, Procedure spkrOBBirthLog, Line 205
Column '#QUERY.ValueInfo' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
PortletPaulfreelancerCommented:
as I haven't recently referenced #QUERY,ValueInfo  L cannot comment.

While this may appear silly to you, we don't actually know what line 205 is as there could be lots of changes or your formatting is different etc.

If you want assistance you have to provide the whole stored proc code (in a code block)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kvrogersAuthor Commented:
I have been sick recently and will enter the entire ode on Monday when I return to work.
0
kvrogersAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for kvrogers's comment #a40325044

for the following reason:

He really helped with the solution.  We found the best way was to start from scratch because we had soooo much in this report.
0
Anthony PerkinsCommented:
Re-opening this question to allow the author to select a more appropriate comment as the solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.