OraFAQ Forum: SQL & PL/SQL » required date month year query

Home » SQL & PL/SQL » SQL & PL/SQL » required date month year query (oracle 10g express edition)

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

required date month year query [message #643266]

Fri, 02 October 2015 04:15

sudheerips
Messages: 7
Registered: September 2015
Location: visakhapatnam

Junior Member

hi,
I have a employees table.
My requirement is
who are joined in sep/1999.I want like this(sep/1999).
if i try to get that values i am getting like this(23/sep/1999) date,month and year also.
but month and year are not getting.

please tell me

[Updated on: Fri, 02 October 2015 04:18]

Report message to a moderator

Re: required date month year query [message #643267 is a reply to message #643266]

Fri, 02 October 2015 05:08

bugfox
Messages: 18
Registered: October 2010

Junior Member

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm

Report message to a moderator

Re: required date month year query [message #643269 is a reply to message #643266]

Fri, 02 October 2015 06:35

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

sudheerips wrote on Fri, 02 October 2015 04:15

hi,
I have a employees table.
My requirement is
who are joined in sep/1999.I want like this(sep/1999).
if i try to get that values i am getting like this(23/sep/1999) date,month and year also.
but month and year are not getting.

please tell me

Can you spot the error in code that you cannot see?
Neither can anyone else.

We have to know the code you are using, as well as the data type of the date column. If that data type is anything other than DATE, then you have a failed design, but it can probably be worked around.

Report message to a moderator

Re: required date month year query [message #643273 is a reply to message #643266]

Fri, 02 October 2015 10:11

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Use SQL*Plus and copy and paste your session, the WHOLE session.

[Updated on: Fri, 02 October 2015 10:11]

Report message to a moderator

Re: required date month year query [message #643291 is a reply to message #643273]

Fri, 02 October 2015 14:03

Bill B
Messages: 1971
Registered: December 2004

Senior Member

if you want the number of people who joined in september 1999 then

select to_char(hiredate,'Mon/yyyy') month,count(*)
from emps
where hiredate between to_date('09011999','mmddyyyy') and last_day(to_date('09011999','mmddyyyy'));

If it is other than that, use a similar idea

Report message to a moderator

Re: required date month year query [message #643460 is a reply to message #643291]

Thu, 08 October 2015 00:13

sudheerips
Messages: 7
Registered: September 2015
Location: visakhapatnam

Junior Member

query not working
shows error
ORA-00937: not a single-group group function
i attached the image file.
please see

Attachment: Capture.JPG
(Size: 54.84KB, Downloaded 775 times)

Report message to a moderator

Re: required date month year query [message #643461 is a reply to message #643460]

Thu, 08 October 2015 00:15

Michel Cadot
Messages: 68633
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just add the appropriate GROUP BY clause.

Report message to a moderator

Re: required date month year query [message #643482 is a reply to message #643460]

Thu, 08 October 2015 06:42

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

sudheerips wrote on Thu, 08 October 2015 00:13

query not working
shows error
ORA-00937: not a single-group group function
i attached the image file.
please see

Why attach an image? Many people are blocked from such links. Even if not it is an unnecessary inconvenience. Do you not know how to copy text from a command window and paste that text directly into you messages? If you were unaware that you can do that, then now you know and I leave it as an exercise for the student to research how to to do it. You shouldn't have to ask anyone except Google.

Report message to a moderator

Re: required date month year query [message #643484 is a reply to message #643460]

Thu, 08 October 2015 07:46

Bill B
Messages: 1971
Registered: December 2004

Senior Member

sudheerips wrote on Thu, 08 October 2015 01:13

query not working
shows error
ORA-00937: not a single-group group function
i attached the image file.
please see

Sorry

select to_char(hiredate,'Mon/yyyy') month,count(*)
from emps
where hiredate between to_date('09011999','mmddyyyy') and last_day(to_date('09011999','mmddyyyy'))
group by to_char(hiredate,'Mon/yyyy');

Report message to a moderator

Re: required date month year query [message #643486 is a reply to message #643484]

Thu, 08 October 2015 08:00

cookiemonster
Messages: 13919
Registered: September 2008
Location: Rainy Manchester

Senior Member

If hiredate can have times other than midnight then that should be:

where hiredate >= to_date('09011999','mmddyyyy')
and hiredate < last_day(to_date('09011999','mmddyyyy')) + 1

Otherwise anything after midnight on the last day will be ignored.

Report message to a moderator

Re: required date month year query [message #643487 is a reply to message #643486]

Thu, 08 October 2015 08:02

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Agreed, I just assumed that a hire date would be a truncated date.

Report message to a moderator

Previous Topic:	query correction required
Next Topic:	set target table flag as D if rows deleted at source

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 15 23:56:27 CDT 2024