Solved

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

Posted on 2014-09-11
25
110 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

15 Experts available now in Live!

Get 1:1 Help Now