OraFAQ Forum: SQL & PL/SQL » SUM CASE date difference calculation

Home » SQL & PL/SQL » SQL & PL/SQL » SUM CASE date difference calculation (Oracle SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SUM CASE date difference calculation [message #639176]

Thu, 02 July 2015 10:23

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Hi,

I am trying to calculate the sum of the number of days from a table. I have done the following but getting an error.

SUM(CASE 
WHEN date_out>'30-Apr-2015' 
THEN '30-Apr-2015'-trunc(date_in)  -- error on this line 
ELSE round((date_out-date_in)+1) END)  AS DAYS

The error message I get is:
ORA-00932: inconsistent datatypes: expected CHAR got DATE

Is there a way to convert the date into a number to do the calculation? Something similar to the DATEDIFF function in SQL Server would be ideal.

Any help greatly appreciated,

Many thanks,
K

Report message to a moderator

Re: SUM CASE date difference calculation [message #639179 is a reply to message #639176]

Thu, 02 July 2015 10:27

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

bowgieman wrote on Thu, 02 July 2015 20:53

[code]
date_out>'30-Apr-2015'

'30-Apr-2015' is NOT a DATE, it is a STRING. Use TO_DATE to explicitly convert a literal into date, and make sure you use proper format model and NLS_DATE_LANGUAGE.

Report message to a moderator

Re: SUM CASE date difference calculation [message #639182 is a reply to message #639179]

Thu, 02 July 2015 10:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

DECLARE
TODAY DATE;
YESTERDAY DATE;
DIFF NUMBER;
BEGIN
DIFF := TODAY - YESTERDAY; -- UNIT OF MEASURE IS DAYS
END;

Report message to a moderator

Re: SUM CASE date difference calculation [message #639183 is a reply to message #639179]

Thu, 02 July 2015 10:39

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Thanks for your help Lalit,

I now get the error ORA-01830: date format picture ends before converting entire input string. What should the NLS_DATE_LANGUAGE be?

SUM(CASE WHEN date_out>'30-Apr-2015' or date_out is null 
THEN (to_date('30-Apr-2015','J')-date_in) 
ELSE round((date_out-date_in)+1) END)  AS days

Thanks,
K

Report message to a moderator

Re: SUM CASE date difference calculation [message #639187 is a reply to message #639183]

Thu, 02 July 2015 10:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

bowgieman wrote on Thu, 02 July 2015 21:09

Thanks for your help Lalit,

I now get the error ORA-01830: date format picture ends before converting entire input string. What should the NLS_DATE_LANGUAGE be?
SUM(CASE WHEN date_out>'30-Apr-2015' or date_out is null 
THEN (to_date('30-Apr-2015','J')-date_in) 
ELSE round((date_out-date_in)+1) END)  AS days

You still have a string instead of date.

HINT : The comparison condition

P.S. : To know more about NLS_DATE_LANGUAGE, please read the documentation on DATATYPE first. And make sure you pay more heed for DATE datatype. You know what, "Apr" is not the same month name for someone who isn't using NLS_DATE_LANGUAGE as AMERICAN(Just an example).

Edit : Silly typo

[Updated on: Thu, 02 July 2015 10:46]

Report message to a moderator

Re: SUM CASE date difference calculation [message #639191 is a reply to message #639183]

Thu, 02 July 2015 10:49

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

As Lalit says, you need to investigate the TO_DATE function more thoroughly. Its syntax is TO_DATE(<date>, <format>)

In your example, try TO_DATE('30-Apr-2015', 'DD-Mon-YYYY')

Report message to a moderator

Re: SUM CASE date difference calculation [message #639192 is a reply to message #639187]

Thu, 02 July 2015 10:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If you are only concerned with the date and not any other element, then I suggest you to use the ANSI date literal.

From the docs,

Quote:

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD')

Report message to a moderator

Re: SUM CASE date difference calculation [message #639193 is a reply to message #639192]

Thu, 02 July 2015 10:55

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Thank you guys. That has worked.

SUM(CASE WHEN date_out>'30-Apr-2015' or date_out is null 
THEN round((to_date('30-Apr-2015','DD-MON-YYYY')-date_in)+1) 
ELSE round((date_out-date_in)+1) END)  AS days

Report message to a moderator

Re: SUM CASE date difference calculation [message #639194 is a reply to message #639193]

Thu, 02 July 2015 10:58

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

Thanks for the feedback. However, you should also use TO_DATE in your CASE statement:

CASE WHEN date_out>'30-Apr-2015'

Report message to a moderator

Re: SUM CASE date difference calculation [message #639199 is a reply to message #639194]

Thu, 02 July 2015 11:42

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Thanks

Report message to a moderator

Re: SUM CASE date difference calculation [message #639213 is a reply to message #639199]

Thu, 02 July 2015 18:12

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Save yourself some keystrokes, use date literals. Use DATE '2015-04-30' instead of lengthy to_date('30-Apr-2015','DD-MON-YYYY').

SY.

[Updated on: Thu, 02 July 2015 18:13]

Report message to a moderator

Re: SUM CASE date difference calculation [message #639214 is a reply to message #639213]

Thu, 02 July 2015 18:20

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

what date is '10-11-12'?
I'll give you 6 guesses because the first 5 will be wrong.

Report message to a moderator

Re: SUM CASE date difference calculation [message #639234 is a reply to message #639214]

Fri, 03 July 2015 05:08

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Enlighten me, I only have four...

DD-MM-YY
MM-DD-YY
YY-MM-DD
YY-DD-MM

Report message to a moderator

Re: SUM CASE date difference calculation [message #639235 is a reply to message #639234]

Fri, 03 July 2015 05:10

bowgieman
Messages: 6
Registered: July 2015
Location: London

Junior Member

Depending on format, it could also be...

MM-YY-DD
DD-YY-MM - 6th guess

Report message to a moderator

Re: SUM CASE date difference calculation [message #639236 is a reply to message #639234]

Fri, 03 July 2015 05:11

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You miss

DD-YY-MM
MM-YY-DD

[EDIT] OK then, you found them yourself.

[Updated on: Fri, 03 July 2015 05:12]

Report message to a moderator

Re: SUM CASE date difference calculation [message #639237 is a reply to message #639234]

Fri, 03 July 2015 05:11

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

It's the combination. 10, 11 and 12 are all valid for dates, months or years Smile

Smile

Report message to a moderator

Re: SUM CASE date difference calculation [message #639256 is a reply to message #639237]

Fri, 03 July 2015 11:07

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I think Ed's article is good to remove some confusion about date format in this case... https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Ah! Ed didn't use the ANSI date literal example in his article, I just requested him if he could add it too.

[Updated on: Fri, 03 July 2015 11:11]

Report message to a moderator

Previous Topic:	construct full date based on value yyyymm
Next Topic:	Data selection on basis of columns from two consecutive rows (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 27 05:53:22 CDT 2026