PLSQL dateserial 1

[satinsilhouette]

Hi,
I need to run a query based off a between date. Here is what I am used to writing in SQL, but it seems not to work against Oracle. Can anyone help?

POST_DATE BETWEEN dateserial(year(dateserial(year(datadate),month(datadate),1)-1),1,1)AND
DATESERIAL(year(datadate),month(datadate),1) - 1

I am receiving a Invalid Identifier error on the dateserial from Oracle.

Thanks,
muffntuf

Thanks so much!
satinsilhouette

 

[SantaMufasa]

Muff,

Unfortunately, besides the "group" functions (such as SUM, MIN, MAX, AVG, COUNT, et cetera), there are no ANSI SQL functions (such as "DATESERIAL") that you can depend on crossing vendor boundaries. Oracle does not have a function by that name.

If you can give us a non-syntactical explanation of what you want to result (where you have used the "DATESERIAL" function), I'm certain we can post a much simpler resolution for you (since Oracle's DATE functions are amongst the most powerful in the industry).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[BJCooperIT]

dateserial is not an Oracle function - if you can tell me what it does, perhaps we can figure out what you need. When comparing date fields In Oracle, you can code statements like:

POST_DATE BETWEEN datadate-30 and datadate

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[satinsilhouette]

I need to get the Month to date summaries of an accounts receivable. And then I need to get Year to date as well.

Thanks
SantaMufasa

Thanks so much!
satinsilhouette

 

[SantaMufasa]

Muff,

Sorry...not quite enough info. I infer from your "BETWEEN" usage that you have a WHERE clause preceding "POST_DATE BETWEEN...". It appears that you might want something similar to the following (syntactically incomplete) logic:

WHERE POST_DATE BETWEEN <some "datadate" reference>
  AND <some "datadate" reference offset by some period of time>...

Am I getting close?

Could you please confirm the following:

1) What is the Oracle datatype of your column, "datadate"?
2) What are the periods of time you want to offset against "datadate" in either/both of the BETWEEN references?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

I don't see datadate as being a function of Oracle? Am I wrong? That is more a crystal function.

Thanks so much!
satinsilhouette

 

[SantaMufasa]

Prior to your disclosing that you are using CR, "datadate" appeared to me to be a column from a table. Since you are the one coding this, you should be able to tell us what "datadate" is.

I am not a CR aficionado, so I'm not familiar with CR's tokens and built-in functions. Could "datadate" be a CR item similar to Oracle's SYSDATE?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

You are correct SantaMufasa - I am sorry it is in a where clause. I need the where clause to get me to Month to date data as well as Year to date.

And you are also correct,my main reporting tool is CR, but I also excel at optimizing by making queries that go directly against the database. This month's challenge is directly against Oracle using their PLSQL query tool. Otherwise I would have had this done a long time ago. But Oracle is sometimes complicated in some of its function usage.

So in a simpler manner:

Where Post_Date between parameterdate and parameter date. Parameterdate is a feed I can send from CR. But for right now I need to send in a date range.

Here is what you recommended once upon a time for me:

POST_DATE BETWEEN ('01-01-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
and ('01-21-2006 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

When I use this I get "Missing a parenthesis.


Thanks so much!
satinsilhouette

 

[SantaMufasa]

In that case, you can accomplish the same effect with the following code:

...WHERE POST_DATE BETWEEN to_date('01-01-2006', 'DD-MM-YYYY') 
   and to_date('01-21-2006', 'DD-MM-YYYY')...

This presumes that "POST_DATE" is an Oracle DATE expression. Also, TO_DATE expressions default to "00:00:00"...that's why I truncated those pieces from your code.

Let us know if you have additional questions on this topic.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

Thanks so much!
Can I ask a question regarding a post earlier (I think last week) similar to this one.

accept beg_date prompt "Enter the start date (DD-MON-YYYY): "
accept end_date prompt "Enter the end date (DD-MON-YYYY): ";

Allows a parameter from the PLSQL query box to be entered, but where do you put this piece of code, anywhere, at the beginning, before the where clause?

Thanks!

Thanks so much!
satinsilhouette

 

[satinsilhouette]

Also if I want to set that where clause to always look at the month to date, is there a sysdate formula as referenced earlier?

Thanks so much!
satinsilhouette

 

[satinsilhouette]

BETWEEN TO_DATE(MONTH(SYSDATE),'01', YEAR(SYSDATE),'MM-DD-YYYY') AND TO_DATE(SYSDATE, 'MM-DD-YYYY')

I am getting a YEAR invalid identifier error with this. What syntactically am I doing wrong?

Thanks!

Thanks so much!
satinsilhouette

 

[SantaMufasa]

Allows a parameter from the PLSQL query box to be entered...

I am not aware of something called a "PL/SQL query box"...that must be some sort of user-interface accommodation from a third-party package you are using. I am not aware of any mechanism in native PL/SQL that accommodates direct user input.

SQL*Plus has the infrastructure to do what you mention ("ACCEPT...PROMPT...") and then when you run PL/SQL blocks (scripts) from SQL*Plus, the SQL*Plus environment allows for use of both replacement items ("&some_name") and bind variables (":some_name") within the PL/SQL block.

The placement of the SQL*Plus "ACCEPT...PROMPT..." syntax dictates that it appears prior to the code block that uses the ACCEPTed item.

I want to set that where clause to always look at the month to date...

Here is a code fragment that accomplishes month-to-date filtering:

...WHERE <some_date> BETWEEN trunc(sysdate,'MM') AND sysdate...

Let us know if this answers your questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[satinsilhouette]

Thanks!

Thanks so much!
satinsilhouette