Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Problem with DATE and TIME

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

New User


Joined: 04 Oct 2011
Posts: 6
Location: india

PostPosted: Wed Jul 01, 2015 1:23 pm    Post subject: Problem with DATE and TIME
Reply with quote

Hi,
I have a table from where I need to extract the order details. The requirement is such that the orders details should be older than todays date i.e less than current date and time less than 2200 HRS from the previous day.

But the problem is that whenever I use CREATE_TIME < '22.00.00' it removes all the orders between 22-23.59 for all the dates and not just previous day.

I am using the conditions as below:

AND CREATE_DTE < CURRENT_DATE
AND CREATE_TIME < '22.00.00'

Can anyone help me out in extracting all the details for orders created less than current date and time less than 2200 HRS from the previous day and not every day.

The datatypes are as below:

CREATE_DTE DATE
CREATE_TIME TIME

Thanks.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jul 01, 2015 1:47 pm    Post subject:
Reply with quote

simple:
Code:
         CREATE_DTE < CURRENT_DATE - 1 days
OR  (    create_DTE  = CURRENT_DATE - 1 days
    AND CREATE_TIME < '22.00.00'

more performant:
Code:
AND CREATE_DTE < CURRENT_DATE
AND NOT ( CREATE_DTE = CURRENT_DATE - 1 days
          AND CREATE_TIME >= '22.00.00' )

date-functions:
Code:
and timestamp (create_dte, create_time) < timestamp(current_date - 1 days, '22:00:00')
Back to top
View user's profile Send private message
Smita.t2

New User


Joined: 17 Apr 2012
Posts: 22
Location: Bangalore

PostPosted: Wed Jul 01, 2015 2:12 pm    Post subject:
Reply with quote

please try

select * from table
Where CREATE_DTE <= CURRENT_DATE - 2
or ((CREATE_DTE < CURRENT_DATE-1)
and (CREATE_TIME < '22.00.00'))
with ur;
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Wed Jul 01, 2015 4:42 pm    Post subject:
Reply with quote

Well, of course it will remove those from all previous dates as you say "less than today's date" but do not say "but greater than 2 days ago". You need "equal to (today's date -1 day)"
Back to top
View user's profile Send private message
Smita.t2

New User


Joined: 17 Apr 2012
Posts: 22
Location: Bangalore

PostPosted: Wed Jul 01, 2015 4:47 pm    Post subject:
Reply with quote

You are correct Nic.
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. Get a job submitted itself every time... arunsoods JCL & VSAM 3 Tue Nov 22, 2016 4:05 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us