|
View previous topic :: View next topic
|
| Author |
Message |
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
If the column DATE_ORIG_INCPT > ‘2011-12-31’ then the first two digits of the same column need to be changed to ‘19’. I’m not sure what the syntax would be for this type query. The column is a 10 character date field.
DB2 Table Name: CPXPDETT
Column Name: DATE_ORIG_INCPT
Please help me. |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Subbu,
The column DATE_ORIG_INCPT is character data type column and you want first 2 digits to be changed to 19, when it satisifies the condition.
Output : 1911-12-31
is this the output you are expecting.....
| Code: |
select '19'||SUBSTR ( DATE_ORIG_INCPT, 3 ) from CPXPDETT
where DATE_ORIG_INCPT > ‘2011-12-31’ |
You need to be more descriptive....
Thanks,
Sushanth |
|
| Back to top |
|
|
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
Hi Sushant,
Sorry for the confusion.
Requirement: If the DATE_ORIG_INCPT (FORMAT: CCYY-MM-DD) is GREATER THAN "2011-12-31" then that should be changed to "19YY-MM-DD" in the table.
YY-MM-DD SHOULD BE SAME AS ORIGINAL. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Subbu,
You can use an UPDATE statement do that process
| Code: |
UPDATE CPXPDETT
SET DATE_ORIG_INCPT = '19'||SUBSTR ( DATE_ORIG_INCPT, 3 )
where DATE_ORIG_INCPT > ‘2011-12-31’ |
*Statement not tested
Just wondering why do you want to change the century ?
Thanks,
Sushanth |
|
| Back to top |
|
|
Kjeld
Active User
Joined: 15 Dec 2009
Posts: 365
Location: Denmark
|
|
|
|
If your column is in DATE format you could use the following construction (not tested):
| Code: |
UPDATE CPXPDETT
SET DATE_ORIG_INCPT = DATE_ORIG_INCPT - 100 YEARS
where DATE_ORIG_INCPT > ‘2011-12-31’
|
|
|
| Back to top |
|
|
subbu1522
New User
Joined: 11 Sep 2008
Posts: 29
Location: USA
|
|
|
|
| Code: |
UPDATE BATCHTCP.CPXPDETT
SET DATE_ORIG_INCPT = 19||SUBSTR(DATE_ORIG_INCPT,3)
WHERE DATE_ORIG_INCPT > 2011-12-31; |
The above query is working in TERADATA but this is not working in DB2. It is giving me an error.
Could you please help me. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| subbu1522 wrote: |
| Code: |
UPDATE BATCHTCP.CPXPDETT
SET DATE_ORIG_INCPT = 19||SUBSTR(DATE_ORIG_INCPT,3)
WHERE DATE_ORIG_INCPT > 2011-12-31; |
The above query is working in TERADATA but this is not working in DB2. It is giving me an error.
Could you please help me. |
Why did you leave out the single quotes that Sushanth specified? Why did you not copy and paste the error message, or at least give the SQLSTATE? |
|
| Back to top |
|
|
|
|
