|
View previous topic :: View next topic
|
| Author |
Message |
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
Hi All
I have a table in which a column is a char field of length 20 in a table.
It contains date in first 10 positions and month in next 2 positions.
| Code: |
COL1
------------
2011-01-3106 |
I'm trying to add the (SUBSTR(COL1,11,2) to (SUBSTR(COL1,1,10)and get the result as
| Code: |
COL1
--------------------
2011-01-012011-06-31 |
Is it feasible to add like this? How to achieve this? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you think more about this. . .
There is no month 6 day 31. . . I suspect it would not be good to generate invalid data. |
|
| Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.
|
|
|
|
Untested:
UPDATE TABLENAME
SET COL1 = CHAR(SUBSTR(COL1,1,10)||CHAR(DATE(SUBSTR(COL1,1,10) + INT(SUBSTR(COL1,11,2)) MONTHS))
WHERE ?????; |
|
| Back to top |
|
|
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
Excuse me for the confusion, it should be
| Code: |
COL1
--------------------
2011-01-012011-06-30 |
|
|
| Back to top |
|
|
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
Ronald Burr,
I tried with your query. it didn't work, Thanks for your effort |
|
| Back to top |
|
|
HameedAli
Active User
Joined: 16 Apr 2009
Posts: 151
Location: India
|
|
|
|
The following select clause helped me in achieving this.
| Code: |
SELECT DTE + MTHS MONTHS
FROM (
SELECT DATE(SUBSTR(COL1,1,10)) AS DTE ,
INTEGER(SUBSTR(COL1,11,2)) AS MTHS
FROM Table1 ) AS Table2 |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
talk about moving targets.
first you want to update a column: you provided expected results which showed an updated column.
next you complain about sql that was posted as untested.
then you provide a solution that does not satisfy your required results: you wanted to update a column. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
strong warning
for date related computation ( adding subtracting days, months, years )
before worrying about the technical issue ( as sadly everybody does over here )
everybody, repeat everybody, should be concerned more about the organization standards and practices...
the issue mostly relates to last day of the month and leap year processing..
there are general practices in place,
( it is accepted that adding one month to the Jan 29/30/31 will result on feb 28/29 )
the issue should anyway be brought to the attention of the powers of Your organization
for awareness and proper legalese ! |
|
| Back to top |
|
|
|
|
