Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Timestamp with time zeros

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Tue Nov 06, 2007 12:31 am    Post subject: Timestamp with time zeros
Reply with quote

Hi,

Is there any function that could be used to return the current timestamp with time as zeros.

2007-11-05-00.00.00.000000

Thanks in advance,
Aneesh
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Nov 06, 2007 1:21 am    Post subject: Re: Timestamp with time zeros
Reply with quote

Aneesh wrote:
Hi,

Is there any function that could be used to return the current timestamp with time as zeros.

2007-11-05-00.00.00.000000

Thanks in advance,
Aneesh


1. A timestamp with a time of 0 wouldn't be the current timestamp.

2. What language are you using COBOL, ASM, etc?

3. Where are you trying to get it from, ZOS, CICS, DB2?
Back to top
View user's profile Send private message
Phrzby Phil

Active Member


Joined: 31 Oct 2006
Posts: 965
Location: Richmond, Virginia

PostPosted: Tue Nov 06, 2007 2:27 am    Post subject:
Reply with quote

Can you come up with nested functions that:

convert current timestamp to char, substr the left part you want, concatenate the zeroes you want, with appropriate delimiter chars, then convert the whole thing back to timestamp?
Back to top
View user's profile Send private message
Aneesh

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Tue Nov 06, 2007 3:26 am    Post subject: Reply to: Timestamp with time zeros
Reply with quote

Hi,

I apologize for the confusion. I am trying to do an unload from DB2 using an unload utility on mainframe ZOS. I have a particular scenario where i need the unload criteria based on created timestamp with the time zeroed out.

I can create the the field by concatenating the date with zeros to create the format - is there any function in DB2 that would allow this.

Thanks,
Aneesh.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 06, 2007 3:46 am    Post subject:
Reply with quote

Hello,

In a select, (IIRC) you can have the query return selected columns as well as literals.

You might try that. I've not tested this in db2 (i'm not yet connected<g>), but do seem to recall doing this in other "standard" sql.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Nov 06, 2007 3:56 am    Post subject:
Reply with quote

I think this might work, I don't have DB2 available right this minute but this should be right.
Code:
WHERE TIME_STAMP >= TIMESTAMP(CHAR(CURRENT DATE) CONCAT '-00.00.00.000000')
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10326
Location: italy

PostPosted: Tue Nov 06, 2007 4:10 am    Post subject: Reply to: Timestamp with time zeros
Reply with quote

my db2 is quite rusty,

but what' s wrong in using the built in function DATE

WHERE DATE(TIME_STAMP) >= SELECT DATE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY

or

WHERE DATE(TIME_STAMP) >= SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY

cancel this post if I am blatantly wrooong
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Nov 06, 2007 4:52 am    Post subject: Re: Reply to: Timestamp with time zeros
Reply with quote

enrico-sorichetti wrote:
my db2 is quite rusty,

but what' s wrong in using the built in function DATE

WHERE DATE(TIME_STAMP) >= SELECT DATE(CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY

or

WHERE DATE(TIME_STAMP) >= SELECT CURRENT DATE FROM SYSIBM.SYSDUMMY

cancel this post if I am blatantly wrooong


These are the correct format but, if the time_stamp column is indexed I think these will cause a table scan

Code:
WHERE DATE(TIME_STAMP) >= CURRENT DATE

or

WHERE DATE(TIME_STAMP) >= CURRENT DATE
Back to top
View user's profile Send private message
ruodeer

New User


Joined: 06 Jul 2007
Posts: 59
Location: home

PostPosted: Wed Nov 14, 2007 2:09 pm    Post subject:
Reply with quote

Code:
SELECT CONCAT(CHAR(DATE(TIME_STAMP)),'-00.00.00.000000')
FROM TABLE
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. 00D3003B - time outs in DB2 when invo... chavinash2004 DB2 2 Mon Oct 09, 2017 4:39 pm
No new posts VSAM RLS Wait time blayek JCL & VSAM 2 Mon Oct 02, 2017 9:05 pm
No new posts Comparing 2 Files using Current time arunsoods SYNCSORT 9 Fri Sep 22, 2017 6:00 pm
No new posts Regarding time parameter shanthi gude JCL & VSAM 7 Mon Sep 04, 2017 2:31 pm
No new posts editting the file by inserting zeros ... pramodrsingh DFSORT/ICETOOL 9 Mon Aug 21, 2017 5:58 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us