| Author |
Message
|
| Fresher_Newbie |
 Posted: Fri Jun 26, 2015 10:26 am Post subject: Convert ESQL TIMESTAMP format to Oracle TIMESTAMP format |
 |
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
Hello folks,
Is there any way I can convert the value returned by the CURRENT_TIMESTAMP function (Ex: 2015-06-26 08:30:00.000000) in ESQL to ORACLE TIMESTAMP format (Ex: 26-JUN-15 08.30.00.000000000 AM)? |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Fri Jun 26, 2015 10:42 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
Cast to character with format clause? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 26, 2015 10:47 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Oracle should convert a timestamp into a timestamp.
Neither of which should have a format. |
|
| Back to top |
|
|
| Fresher_Newbie |
| Posted: Fri Jun 26, 2015 11:49 am Post subject: |
|
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
@fjp
Thanks, I've done the same as shown below:
SET CUR_T = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'dd-MMM-yy hh.mm.ss.SSSSSSSSS');
But also need to include the AM/PM part. How do I do that? Excuse my ignorance, but I'm really new to this. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jun 26, 2015 12:02 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| Fresher_Newbie wrote: |
@fjp
Thanks, I've done the same as shown below:
SET CUR_T = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'dd-MMM-yy hh.mm.ss.SSSSSSSSS');
But also need to include the AM/PM part. How do I do that? Excuse my ignorance, but I'm really new to this. |
Look up the documentation. I believe you have to use HH vs hh and add A somewhere in the template...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Fresher_Newbie |
| Posted: Fri Jun 26, 2015 12:09 pm Post subject: |
|
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
HH gives you the 24 hour format
hh gives you the 12 hour format, but neither with an AM/PM marker.
Hmm, all right I'll look it up, again. Thanks! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jun 26, 2015 12:13 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| fjb_saper wrote: |
| Fresher_Newbie wrote: |
@fjp
Thanks, I've done the same as shown below:
SET CUR_T = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'dd-MMM-yy hh.mm.ss.SSSSSSSSS');
But also need to include the AM/PM part. How do I do that? Excuse my ignorance, but I'm really new to this. |
Look up the documentation. I believe you have to use HH vs hh and add A somewhere in the template... |
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Jun 26, 2015 12:24 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Fresher_Newbie wrote: |
Thanks, I've done the same as shown below:
SET CUR_T = CAST(CURRENT_TIMESTAMP AS CHARACTER FORMAT 'dd-MMM-yy hh.mm.ss.SSSSSSSSS'); |
Why?
What happens when you don't bother casting it at all, just put the plain timestamp into the insert statement? |
|
| Back to top |
|
|
| Fresher_Newbie |
| Posted: Fri Jun 26, 2015 12:41 pm Post subject: |
|
|
Novice
Joined: 24 Jun 2015
Posts: 13
|
Addidng the 'a' did it.
mqjeff - I needed to use it in the SELECT statement to retrieve some records off the ORACLE database, where the TIMESTAMP coloumn is stored in dd-MMM-yy hh.mm.ss.SSSSSSSSS format. It wouldn't work with the plain timestamp.
Anyway, I got it now. Thanks both of you. |
|
| Back to top |
|
|
| Simbu |
| Posted: Mon Jun 29, 2015 3:30 am Post subject: |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
| Hi, There is no ESQL data type available for Oracle TIMESTAMP datatype. Oracle Datatype DATE will map to ESQL TIMESTAMP automatically. |
|
| Back to top |
|
|
|
|
