Solved

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

Posted on 2014-09-11
25
108 Views
Last Modified: 2014-10-09
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?
0
Comment
Question by:kvrogers
  • 11
  • 9
  • 2
  • +2
25 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40317727
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
 
LVL 2

Expert Comment

by:Sytech Solutions
ID: 40317728
Try using 110 instead of 112 in your CONVERT function.

Hope that helps.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40317730
Now that does assume ValueInfo is a Datetime field
0
 

Author Comment

by:kvrogers
ID: 40317811
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
 

Author Comment

by:kvrogers
ID: 40317863
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318414
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
 

Author Comment

by:kvrogers
ID: 40318424
How about this:

CASE WHEN MAX(PEDTEDC.ValueInfo) IS NOT NULL
        THEN CONVERT (VARCHAR,MAX(PEDTEDC.ValueInfo), 101) ELSE ' ' END AS EDC
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318446
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40318798
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
 

Author Comment

by:kvrogers
ID: 40319424
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40319602
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
 

Author Comment

by:kvrogers
ID: 40319748
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40320208
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40320254
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40320260
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
 

Author Comment

by:kvrogers
ID: 40323509
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
 

Author Comment

by:kvrogers
ID: 40323575
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
 

Author Comment

by:kvrogers
ID: 40323809
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40324410
>>"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
 

Author Comment

by:kvrogers
ID: 40325044
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40325059
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
 

Author Comment

by:kvrogers
ID: 40333982
I have been sick recently and will enter the entire ode on Monday when I return to work.
0
 

Author Comment

by:kvrogers
ID: 40351280
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40351281
Re-opening this question to allow the author to select a more appropriate comment as the solution.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now