|
View previous topic :: View next topic
|
| Author |
Message |
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
Hi,
While running an SQL query i have a situation where I took date(timestamp). Now i need to concatenate ';' and t the end of the value.
I tried char(date(timestamp))||char(';'), but showing -181 error.
can you plesae let me know hos to do this concatenation? |
|
| Back to top |
|
 |
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
post your complete query... Have you looked as what is mean by SQLCODE you are getting?
And why date of timestamp why not just use current date?
| Code: |
SELECT (CHAR(CURRENT DATE) || ';') FROM SYSIBM.SYSDUMMY1;
|
|
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
select char(date(update_ts))||char(';') from xyz;
I want the output as 2010-05-06;(including ;) |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
| i am working on different date not just current date |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hi Ashok,
| Quote: |
| i am working on different date not just current date |
So whats the problem use ur column name and table name instead of current date and sysibm.sysdummy table.
| Code: |
| select char(char(date(update_ts))|| ';') from xyz; |
I would suggest you to read Db2 |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
it gives ma and error
---------+---------+---------+---------+---------+---------+---------+--------
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1
OF || IS INVALID |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
sorry this is the error message
DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A DATETIME VALUE
IS NOT A VALID DATETIME VALUE |
|
| Back to top |
|
|
skkp2006
New User
Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India
|
|
|
|
Ashok,
Post the SQL that you have used. We have no idea whats the actual query that you have used.
try this also..
| Code: |
SELECT CHAR(DATE(CURRENT TIMESTAMP))||CHAR(';')
FROM SYSIBM.SYSDUMMY1; |
Syam |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Ashok,
I tried it and its working fine for me
| Code: |
SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM ABC
FETCH FIRST ROW ONLY; |
Just check ur code and post the Sql u tried |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
this is the orginal query with table names changed
select char(date(update_ts))||char(';') from xyz;
I had treied the query for sysdummy and it worked, but not working for the orginal query.Unfortunately i canot paste the original query
Mu origibla query works til select char(date(update_ts)), but when added the second part ||char(';') it fails with
DSNT408I SQLCODE = -181, ERROR: THE STRING REPRESENTATION OF A DATETIME VALUE
IS NOT A VALID DATETIME VALUE |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello Ashok,
Did u compare ur query with the Query provided by me?
UR Query
| Code: |
| select char(date(update_ts))||char(';') from xyz; |
My Query
| Code: |
SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM ABC |
Hope u get it now |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
tried SELECT CHAR(CHAR(DATE(UPDATE_TS))||';')
FROM ABC
as well .... same error |
|
| Back to top |
|
|
ashok_uddaraju
New User
Joined: 21 Feb 2007
Posts: 72
Location: US
|
|
|
|
| i got this......... thanks |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
| Finally :-) |
|
| Back to top |
|
|
|
|
