|
View previous topic :: View next topic
|
| Author |
Message |
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
Hi
I created a tabel whose one of the column value is UPDATE_TIMESTAP this time stamp will be the value when the record has been laoded into the tabel(for example: today if i load a record at 12PM value(UPDATED_TIMESTAMP) loads into table is 2008-11-27-12.33.27.238657)
So now i want issue a delete query where All the records having (UPDATE_TIMESTAMP + 24 months) > TODAY’s date must be deleted from the tabel. i have a confusion with the value loaded and current timestamp valueloaded is in the format of (2008-11-27-12.33.27.238657) and current timestamp is 20081127123327238657 how can i compare both value for deleting the record from table..
FYI i coded as:
EXEC SQL DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CUREENT_TIMESTAMP - 24months ;
will it works or need any corrections |
|
| Back to top |
|
 |
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
Hi
i want to delete records whose timestamp is more than 24months.. how can i proceed to this.
FYI i coded as:
EXEC SQL DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CUREENT_TIMESTAMP - 24months ;
will it works or need any corrections |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Code: |
DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CURRENT TIMESTAMP - 2 YEARS
|
|
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| 24 months will also work ... |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| I think you meant that > sign to be a < sign didn't you? |
|
| Back to top |
|
|
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
But the UPdate_TIMESTAMP is the value which is edited while loading into the table for example;2008-11-27-12.33.27.238657
If i pull the value CUREENT TIMESTAMP the value will be 20081127123327238657, How can we compare a relation between them ? |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
"If i pull the value CUREENT TIMESTAMP the value will be 20081127123327238657,..."
Look again at the format of CURRENT TIMESTAMP in your DB2 manual. |
|
| Back to top |
|
|
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
Even though there is change in time representation format like HH:MM:SS in CURRENT TIME STAMP value, there would be any change in YYYYMMDD field,
I mean: UPDATED TIME STAMP VALUE = 2008-11-27-12.33.27.238657
CURRENT TIME STAMP VALUE = 2008112712:34:45123957
How the date format will be evaluated in expression ? |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| See section 1.1.9 in the DB2 Application Programming and SQL Guide. The format for CURRENT TIMESTAMP is not what you indicate. |
|
| Back to top |
|
|
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
Terry Thank you very much for your assistance.
but unfortunetly i do not have the manual.. If you don't mind can you fwd that to my id e-addr removed per forum rules, so that i will chck and let you know the results. |
|
| Back to top |
|
|
Terry Heinze
JCL Moderator
Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA
|
|
|
|
| Click "IBM Manuals" at the top of this screen. |
|
| Back to top |
|
|
shaikfayaz
New User
Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad
|
|
|
|
Hi Terry,
I am glad to inform you that i am able to Delete the records whose timestamp is greater than 24months
Thank you very much for you support & my pleasure to meet u in this forumn |
|
| Back to top |
|
|
|
|
