RE: [sqr-users] RE: DATEADD function

["Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>]

I disagree with Rajib.  The SQR helpfile shows the units ('day', 'week',
etc.) in lower case.

First, you should check the database table definition to see whether
s.due_date is a date field or a character field with a date in it.

If s.due_date is a date field, you can use it with dateadd.  Alternately,
most databases should let you do date arithmetic as part of the select
statement.

begin-select
s.due_date - 2   &anal_due
from yada, yada

Note that &anal_due starts with an ampersand, not a dollar sign.

If s.due_date is a character field, you have to move it to a variable that
was declared as a date variable, possibly reformatting it on the way.  Then
you can use the date variable in dateadd.

begin-setup
  declare-variable
    date $anal_due $other_date ...
  end-declare
end-setup

Finally, you may want to change the name of your variable for the date when
the analysis is due.  Its current name will make some of your co-workers
snicker, and others sniff in disapproval.

-----Original Message-----
From: Sengupta, Rajib (CAG-CC-Contractor)
[mailto:Rajib.Sengupta@conagrafoods.com]
Sent: Tuesday, September 09, 2003 4:12 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] RE: DATEADD function


I think it is the CAPs- 
try it  let $anal_due = dateadd(&duedate, 'DAY', -2)  
this might not be the case- just give a try

Rajib Sengupta
Systems Analyst and Team Lead
Peoplesoft Financial Consultant
Conagra Business Systems
Phone: 402-595-5749


-----Original Message-----
From: skirklin@stl-inc.com [mailto:skirklin@stl-inc.com]
Sent: Tuesday, September 09, 2003 5:58 PM
To: skirklin@stl-inc.com; sqr-users@sqrug.org
Subject: [sqr-users] RE: DATEADD function



I received two responses for this but neither worked. One was to declare the
variable $anal_due as a date variable. The other was 

"Why not just add to the SQL 
s.due_date - 2 $anal_due"

Does anyone have any other suggestions?

Thanks again,

Sherri


>  -----Original Message-----
> From:         Kirklin, Sherri  
> Sent: Monday, September 08, 2003 10:55 AM
> To:   'sqr-users@sqrug.org'
> Subject:      DATEADD function
> 
> Hello fellow SQR users,
> 
> Here is a portion of some code that I'm using:
> 
> select distinct
> q.queue () on-break print=never level=1 before=bef-q after=aft-q
> qtf.fraction &fraction
> q.name &qname
> s.due_date &duedate
> pj.project_seq  () on-break print=never level=2 before=bef-wo after=aft-wo
> pf.cust_id &customer
> pf.reqnbr &profile
> pf.cs_contact &csc
> pac.acode () on-break print=never level=3 before=bef-acode after=aft-acode
> ps.hsn &hsn
> nvl(pac.charge,-1) &price
> price.charge &listprice
>   let $anal_due = dateadd(&duedate, 'day', -2) 
> from yada yada yada...
> 
> I'm trying to calculate a date 2 days earlier than the s.due_date and this
> is why I'm using the DATEADD function.
> 
> In some cases this is working but in others it is not. The field
> s.due_date is of the format 19-SEP-03 when it comes from the database.
> 
> Can someone suggest a better way of doing this?
> 
> Thanks much,
> 
> Sherri Kirklin
> Sherri Kirklin
> Analyst/Programmer
> STL-Austin
> Ph: (512) 310-5286
> Fax: (512) 244-0160
> skirklin@stl-inc.com
> 



Confidentiality Notice: The information contained in this message is
intended only for the use of the addressee, and may be confidential
and/or privileged. If the reader of this message is not the intended
recipient, or the employee or agent responsible to deliver it to the
intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify the
sender immediately.

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users