Solved

How can I format my date properly inthis query?

Posted on 2009-03-31
2
228 Views
Last Modified: 2013-12-19
Hi There,

I ahve the below query with 3 parameters. How can I properly format the dates? Right now i get the error ORA-01841: (full) year must be between -4713 and +9999, and not be 0?

Thanks,
Rhonda
SELECT     primary_mobile_id AS Primary_Mobile_Num, NVL(b.description, 'Not-rated') AS billable, mobile_id AS Originator, 

                      cntry_num || called_num AS Destination, orig_zone AS Ocean_Region, to_char(call_dt, 'DD/MM/YYYY HH24:MI:SS') AS call_dt, call_dt AS call_date, 

                      customer_code AS CustCode, NVL(SUM(minutes), 0.00) AS Units, NVL(b_uom, 'mins') AS UOM, acct_period AS AcctPeriod, SUM(NVL(total_charge, 0)) 

                      AS Charges, rate_currency AS Currency

FROM         cdr_customer c, billable b

WHERE     primary_mobile_id = :MOBILE1 AND call_dt BETWEEN to_date(':SDATE1' || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')  AND to_date(':EDATE1' || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS') AND c.billable = b.billable (+) AND rev_num >= 0

GROUP BY primary_mobile_id, mobile_id, cntry_num || called_num, orig_zone, to_char(call_dt, 'DD/MM/YYYY HH24:MI:SS'), call_dt, customer_code, 

                      NVL(b_uom, 'mins'), acct_period, invoice_num, rate_currency, NVL(b.description, 'Not-rated')

UNION

SELECT     imn_number AS Primary_Mobile_Num, NVL(b.description, 'Not-rated') AS billable, from_user AS Originator, to_user AS Destination, NVL(from_site, 

                      to_site) AS Ocean_Region, to_char(date_sent, 'DD/MM/YYYY HH24:MI:SS') AS call_dt, date_sent AS call_date, customer_code AS CustCode, 

                      NVL(SUM(billable_units), 0.00) AS Units, billable_uom AS UOM, acct_year AS AcctPeriod, SUM(NVL(space_charge, 0) + NVL(landline_charge, 0) 

                      + NVL(p_ndn_charge, 0)) AS Charges, rate_currency AS Currency

FROM         c_msgs c, billable b

WHERE     imn_number = :MOBILE1 AND date_sent BETWEEN :SDATE1 AND :EDATE1 AND c.billable = b.billable (+) AND rev_num >= 0

GROUP BY imn_number, from_user, to_user, NVL(from_site, to_site), to_char(date_sent, 'DD/MM/YYYY HH24:MI:SS'), date_sent, customer_code, billable_uom, 

                      acct_year, rate_currency, NVL(b.description, 'Not-rated')

ORDER BY 7

Open in new window

0
Comment
Question by:Rhonda Carroll
  • 2
2 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 24028264
are :sdate1 and :edate1  dates or are they strings?

in one query you convert them to dates

  AND call_dt BETWEEN TO_DATE(':SDATE1' || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                     AND  TO_DATE(':EDATE1' || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

but in the other you don't

AND date_sent BETWEEN :sdate1 AND :edate1


are call_dt and date_sent both dates?
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 24028282
in either case you don't want to put the binds inside the quotes

':SDATE1'  ':EDATE1'

if they are dates you don't want to convert them at all
if they are strings you should convert them the same in both cases

BETWEEN TO_DATE(:SDATE1 || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
                     AND  TO_DATE(:EDATE1 || ' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now