Another Case Statement ???

Question

Post reply

Another Case Statement ???

jbalbo

SSCertifiable

Points: 7583
More actions
July 8, 2013 at 1:02 pm

#280109

Hi
Trying to figure out this case statement
if the Dischargedate > enddt or dischargedate is null I want enddt
else
I want dischargedate
Thanks
Joe
case
when
CONVERT(varchar(8), DischargeDate, 112) > CONVERT(varchar(8), enddt, 112) then CONVERT(varchar(8),enddt, 112)
when
NULL(CONVERT(varchar(8), DischargeDate, 112)) then CONVERT(varchar(8),enddt, 112)
else
CONVERT(varchar(8), DischargeDate, 112)
end as DD
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

What are you trying to figure out? What if you think the other way around?

CASE

WHEN CONVERT(varchar(8), DischargeDate, 112) < CONVERT(varchar(8), enddt, 112)

THEN CONVERT(varchar(8),DischargeDate, 112)

ELSE CONVERT(varchar(8), enddt, 112) END as DD

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

That would work.. I think

Sorry I'm thinking Crystal...

Will it still look at is as < when null?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

jbalbo (7/8/2013)
That would work.. I think
Sorry I'm thinking Crystal...
Will it still look at is as < when null?

No, NULL by definition cannot be greater or less than anything. It is an unknown value.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 4

Am I missing something? This seems straight forward...

Case WHEN CONVERT(varchar(8), dischargedate, 112) > CONVERT(varchar(8), enddt,112) OR dischargedate IS NULL THEN CONVERT(varchar(8), enddt, 112)

ELSE CONVERT(varchar(8), dischargedate, 112)

END AS dd

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Maybe an example will show how it works and it will be up to you if it does what it is supposed to do.

DECLARE @test-2 TABLE(

DischargeDatedatetime NULL,

enddtdatetime NULL)

INSERT @test-2 VALUES

('20130101', '20130202'), --Will evaluate as true

('20130202', '20130101'), --Will evaluate as false

(NULL, '20130202'), --Will evaluate as unknown (not true)

('20130202', NULL), --Will evaluate as unknown (not true)

(NULL, NULL) --Will evaluate as unknown (not true)

SELECTCASE

WHEN CONVERT(varchar(8), DischargeDate, 112) < CONVERT(varchar(8), enddt, 112)

THEN CONVERT(varchar(8),DischargeDate, 112)

ELSE CONVERT(varchar(8), enddt, 112) END as DD

FROM @test-2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 6

Don't we have to work on the assumption that enddt is never going to be null? If that's the case (no pun intended) then the case statement will return null in certain circumstances.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Erin Ramsay (7/8/2013)
Don't we have to work on the assumption that enddt is never going to be null? If that's the case (no pun intended) then the case statement will return null in certain circumstances.

We could make assumptions, but we could be wrong. That's why I posted an example with almost every possible scenario (except when both dates are the same, but it wouldn't matter which date we use) to show how will the code work. It could be used as well for any proposed solution. 😉

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Question: Why are you all converting to varchar(8) to compare dates? Seems like a waste as even with the time in there, today is still today isn't it?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);