Home » SQL & PL/SQL » SQL & PL/SQL » Getting single date ? (merged)
Getting single date ? (merged) [message #309857] Sat, 29 March 2008 06:26 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

I have summary date by using below function i converted in to years.

to_char(summary date,'YYYY')

it showing values like 2007,2008,2009,......2015. My problem is
i want to pick 2009 year only by using summary date.

How can i do it,Which function is help me.
Re: Problem in date conversion? [message #309858 is a reply to message #309857] Sat, 29 March 2008 06:44 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Not sure what you mean, but perhaps you're looking for:

where to_char(summary date,'YYYY') = '2009'
?
Re: Problem in date conversion? [message #309859 is a reply to message #309857] Sat, 29 March 2008 06:49 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Is this what you want:
with data as 
(select to_date('01-03-2008','dd-mm-yyyy') date1 from dual 
union all
select to_date('05-03-2009','dd-mm-yyyy') from dual)
select * from data
where to_char(date1,'yyyy')='2009'


-- a bit late --

regards,

[Updated on: Sat, 29 March 2008 06:50]

Report message to a moderator

Re: Problem in date conversion? [message #309892 is a reply to message #309857] Sat, 29 March 2008 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where extract(year from summary_date) = 2009

Regards
Michel

[Updated on: Sat, 29 March 2008 12:06]

Report message to a moderator

Re: Problem in date conversion? [message #309942 is a reply to message #309892] Sun, 30 March 2008 05:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
or, if there is an index on summary_date:
where summary_date between to_date('01-01-2009', 'dd-mm-yyyy') and to_date('31-12-2009 23:59:59', 'dd-mm-yyyy hh24:mi:ss')

Getting single date ? [message #310324 is a reply to message #309857] Mon, 31 March 2008 23:39 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi

By using this function i create year to_char(Summary_DATE,'YYYY')and it showing values like 2007,2008,2009,....2015, But i want only 2008.
I get this one by using where condition. User don't want to use this where condition.

How can we get this without where condition.
Re: Getting single date ? [message #310333 is a reply to message #310324] Tue, 01 April 2008 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You already asked this question.
What's wrong with the answers you got?

Regards
Michel
Re: Getting single date ? [message #310345 is a reply to message #310324] Tue, 01 April 2008 00:21 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
iN tHAT ANS WE USE WHERE CONDITION, bUT I DONT WANT THE WHERE CONDITION.
Re: Getting single date ? [message #310346 is a reply to message #310324] Tue, 01 April 2008 00:24 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>iN tHAT ANS WE USE WHERE CONDITION, bUT I DONT WANT THE WHERE CONDITION.
So do not use WHERE clause.
What is your problem?
Re: Getting single date ? [message #310355 is a reply to message #310324] Tue, 01 April 2008 00:45 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> How can we get this without where condition.

Is this a homework?

One way would be create a table with one row (2008) or and INNER JOIN it with the original table.
Instead of the table, you may alternatively use subquery "SELECT 2008 FROM DUAL".

As the original code is in another thread, I will let the completion on you.
I am afraid the performance will decrease in comparition with the WHERE clause. Also the tuning possibilities will be very limited. However it may not (when original table contains few rows or rows for a few years) be dramatic.

I just start finding anothor solution without additional JOIN Laughing

[Edit: the sentence about tuning]

[Updated on: Tue, 01 April 2008 00:47]

Report message to a moderator

Previous Topic: mutating error problem
Next Topic: Issues on financial year (multiple merges)
Goto Forum:
  


Current Time: Fri Dec 09 01:52:45 CST 2016

Total time taken to generate the page: 0.19170 seconds