[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: High
  • Security: Private
  • Views: 26
  • Last Modified:

Oracle extract function for datetime. can i use formatting

Is there a formatting option for the oracle extract (datetime) function.  I would like to use month, day, year but only 'RR' / YY' and month in 'MM'. The month is a date data type and i would like to use it in by group by clause. I do not want to return the time.

12/01/17
0
Tay Johnson
Asked:
Tay Johnson
  • 2
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
I don't understand what you are asking but I'm thinking extract isn't what you want.

If you want to query a date column by specific pieces you may invalidate any index usage.

If you have a date column and only want the date without time:  trunc(date_column).  That zeros out the time portion.

If you can provide more detail, we can probably tell you better options.
0
 
awking00Commented:
select trunc(date,'mm) will give you the first day of the month, then you can use to_char(date,'MM/DD/RR') to format

SQL> select to_char(trunc(sysdate,'mm'),'MM/DD/RR') from dual;

TO_CHAR(
--------
02/01/18
0
 
Tay JohnsonAuthor Commented:
I want to group by month in this format. 12/01/18. The month is a date data type.

Im truing to write a quert using a Group by clause with inq_num, s_itm_num, code, month, original_Weight, highest_weight. And im having problems grouping by months.
0
 
slightwv (䄆 Netminder) Commented:
I do not know what you mean with "group by month in this format. 12/01/18".

Try what awking00 posted but without the to_char:

select ...
group by trunc(sysdate,'mm')

If that doesn't help, please provide some sample data and expected results so we can see what you are trying to do.
0
 
Tay JohnsonAuthor Commented:
Thanks
0

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now