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: 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: 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: 1823
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
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm
No new posts Validate date and numeric fields and ... Rick Silvers DFSORT/ICETOOL 6 Thu May 11, 2017 6:51 pm
No new posts DSNACCOX (can it be run on 1 db/ts, t... SRICOBSAS DB2 5 Sat May 06, 2017 12:59 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts Application not run by time HH:MM tri... jzhardy IBM Tools 1 Sun Apr 09, 2017 3:22 pm


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