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
 
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: 1281
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: 26
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: 1922
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: 26
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
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm
No new posts Having a problem FTP'ng to the MF pahiker All Other Mainframe Topics 2 Fri Nov 10, 2017 8:20 pm
No new posts Comparision with current time arunsoods DFSORT/ICETOOL 5 Thu Nov 09, 2017 10:37 am
This topic is locked: you cannot edit posts or make replies. Comparing current time with the time ... arunsoods SYNCSORT 1 Mon Oct 30, 2017 4:07 pm
This topic is locked: you cannot edit posts or make replies. Incrementing current time field by 30... arunsoods DFSORT/ICETOOL 11 Thu Oct 26, 2017 3:22 pm

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