IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Problem with DATE and TIME


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1281
Location: Belgium

PostPosted: Wed Jul 01, 2015 1:47 pm
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: 28
Location: Bangalore

PostPosted: Wed Jul 01, 2015 2:12 pm
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: 2448
Location: Hampshire, UK

PostPosted: Wed Jul 01, 2015 4:42 pm
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: 28
Location: Bangalore

PostPosted: Wed Jul 01, 2015 4:47 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Date conversion using SORT SYNCSORT 4
No new posts Sysncsort - Justification problem SYNCSORT 6
No new posts Modify Several JCLs at the Same Time JCL & VSAM 6
No new posts Date Conversion Y2T to Y2W SYNCSORT 8
No new posts Date format conversion DFSORT/ICETOOL 4
Search our Forums:

Back to Top