| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to Change the Hour,Min, Sec Values in TimeStamp |
« View previous topic :: View next topic » |
| Author |
Message
|
| arunkumar1989 |
 Posted: Tue Jan 13, 2015 3:14 am Post subject: How to Change the Hour,Min, Sec Values in TimeStamp |
 |
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Hi all,
How to change the Hour,Min,Sec values in TimeStamp.
Ex : If we taken as CURRENT_TIMESTAMP
it will display the current date and time
In that i need to change time HH:mm:ss as 00:00:00
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Tue Jan 13, 2015 3:44 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
A TIMESTAMP fiels is just that. A binary represtnation of a date time.
It has no format.
What you do is cast that binary represtantion into a character field using a cast statement.
for example
| Code: |
declare tTimeNow TIMESTAMP CURRENT_TIMESTAMP;
declare cTime Char;
set cTime = cast(tTimeNow as char format 'yyyy-MM-dd HH:mm:ss';
|
The various options for the format string are fully documented in the InfoCentre.
you can go the other way as well.
| Code: |
declare tTimeStamp TIMESTAMP;
declare cTime Char '2014-12-31 23:59:59';
set tTimeStamp = cast(cTime as timestamp format 'yyyy-MM-dd HH:mm:ss';
|
Try it out for yourself.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| arunkumar1989 |
| Posted: Tue Jan 13, 2015 7:04 am Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Thanks smdavies99... I have done this with SUBSTRING
| Code: |
SET ClubOpenTime1=CAST(CURRENT_TIMESTAMP AS CHARACTER);
SET ClubOpenTime1=SUBSTRING(ClubOpenTime1 FROM 12 FOR 10);
SET ClubOpenTime1=ClubOpenTime1 || ' 00:00:00';
SET ClubOpenTime = CAST(ClubOpenTime1 AS TIMESTAMP); |
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jan 13, 2015 7:36 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| arunkumar1989 wrote: |
Thanks smdavies99... I have done this with SUBSTRING
| Code: |
SET ClubOpenTime1=CAST(CURRENT_TIMESTAMP AS CHARACTER);
SET ClubOpenTime1=SUBSTRING(ClubOpenTime1 FROM 12 FOR 10);
SET ClubOpenTime1=ClubOpenTime1 || ' 00:00:00';
SET ClubOpenTime = CAST(ClubOpenTime1 AS TIMESTAMP); |
|
That's an incredible waste of time. You can accomplish everything you're doing with substring by using a format string on the CAST
And the end result is still a timestamp, which has no format. So you've cast things to an unformatted string, maniuplated that string to have the format you want, and then you've cast it back to an unformatted data type.
Dates and Times HAVE NO FORMAT.
Last edited by mqjeff on Tue Jan 13, 2015 8:49 am; edited 1 time in total |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 13, 2015 8:47 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
[quote="mqjeff"]That's an incredible waste of time. You can accomplish everything you're doing with substring by using a format string on the CAST
| mqjeff wrote: |
cially as string manipulation is one of the most expensive operations in ESQL.
[quote="mqjeff"]And the end result is still a timestamp, which has no format. So you've cast things to an unformatted string, maniuplated that string to have the format you want, and then you've cast it back to an unformatted data type.
Dates and Times HAVE NO FORMAT. |
If you want a timestamp value of the current date at midnight, it's much more efficient to modify the timestamp directly without doglegging into CHARACTER and back.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
