Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Subtract 1 day from a date dynamically

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Fri Dec 02, 2011 6:38 pm    Post subject: Subtract 1 day from a date dynamically
Reply with quote

Hi ,

I have an input file which a date field in format '02/11/2011' i need to subtract 1 day from this date and update in a table.

i need an sql which can do this for me as i am using easytrive to do this

there are around 20k records in the file with different dates

currently i am using the below code which is not working -

Code:
select (date (ws-date -1 day ))  from sysibm.sysdummy1


please help
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Dec 02, 2011 7:08 pm    Post subject:
Reply with quote

not working is a rather useless comment, don't you think?
why isn't it working?
you would have received some code, either from the compiler or spufi.

practice in spufi replacing ws-date with current_date until you get the syntax correct for the functions that you are using.

and once you get it correct, put it in the update statement.
get rid of the select and just make it part of the UPDATE Clause for the column.
i don't know how in ezytrieve sql to indicate a host variable,
but in cobol the variable name is prefaced by a colon (icon_smile.gif
Back to top
View user's profile Send private message
Sandesh Kirodian

New User


Joined: 29 Sep 2011
Posts: 6
Location: India

PostPosted: Fri Dec 02, 2011 9:18 pm    Post subject:
Reply with quote

i tried it doing with declaring a cursor i am getting the below error -


Code:
SQL DECLARE DATE_CSR CURSOR FOR +                                       
    £ SQL error, SQLCODE is -418                                       
    £ DSNT408I SQLCODE = -418, ERROR:  A STATEMENT STRING TO BE PREPARED
    £          CONTAINS AN INVALID USE OF PARAMETER MARKERS             
    £ DSNT418I SQLSTATE   = 42610 SQLSTATE RETURN CODE                 
£ SQL errors found                                                     
    SELECT (DATE (:IN-WS-PA-HIST-STR-DTE) - 1 DAY) +                   
    FROM SYSIBM.SYSDUMMY1 +                                             
    FOR FETCH ONLY
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Dec 02, 2011 9:36 pm    Post subject:
Reply with quote

don't know what you are doing or where,

normally parameter markers (place holders) are ?.
don't think we can use alias or variable name on mainframe.
on pc, yes, but it requires the declaration of the variable name to db2.

it appears that you want to execute two sql's.
1. get a date
2. update something with this date
for each of your 20k trigger-records.

why not include the date - 1 day bit in the UPDATE Clause,
and then only have one sql?
Back to top
View user's profile Send private message
Mickeydusaor

Active User


Joined: 24 May 2006
Posts: 258
Location: Salem, Oregon

PostPosted: Sat Dec 03, 2011 12:49 am    Post subject: Correct DB2 command format
Reply with quote

This is the correct format of the command....

Code:
SELECT (DATE(:IN-WS-PA-HIST-STR-DTE)) - 1 DAY 
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us