Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 691
  • Last Modified:

How to have report pull data for current date

How do you set a report to pull data from current date (today) with out entering todays date?
SELECT so_.zone_code,so_.cust_ship_to_id,
               floor(substr(so_.end_time, 1, 2) / 8) end_time_shift, -- first 2 characters = hour
count(so_.end_time)
       FROM csi.so_pick_detail so_
   WHERE (so_.end_date = @variable ('Date'))
          
           
GROUP BY so_.zone_code,  so_.cust_ship_to_id,
                 floor(substr(so_.end_time, 1, 2) / 8)

Open in new window

0
Jbancr1
Asked:
Jbancr1
  • 3
  • 3
1 Solution
 
sujith80Commented:
You can use sysdate to get the current date.
See.
select sysdate from dual;

You may try the below suggestion. I have used a trunc function around the column to make sure that the time components are clipped. L
See whether this does for you.
SELECT so_.zone_code,so_.cust_ship_to_id,
               floor(substr(so_.end_time, 1, 2) / 8) end_time_shift, -- first 2 characters = hour
count(so_.end_time)
FROM csi.so_pick_detail so_
WHERE (trunc(so_.end_date) = trunc(sysdate) )
GROUP BY so_.zone_code,  so_.cust_ship_to_id,
                 floor(substr(so_.end_time, 1, 2) / 8)

Open in new window

0
 
Jbancr1Author Commented:
sujith80
I get an error message when I run the code you provided.
ORA-01003:  No statment parsed:-1003
0
 
sdstuberCommented:
I recommend doing something like this instead of trunc on your end_date column,
if you have indexes on on the end_time column, writing the query like above will
prevent the use of those indexes, as illustrated below can use indexes.

sujith80's statement looks to be correct syntax though, did you miss a character in a cut-n-paste or something small like that?

  SELECT so_.zone_code,
         so_.cust_ship_to_id,
         FLOOR(SUBSTR(so_.end_time, 1, 2) / 8) end_time_shift,       
         COUNT(so_.end_time)
    FROM csi.so_pick_detail so_
   WHERE so_.end_date >= TRUNC(SYSDATE) AND so_.end_date < TRUNC(SYSDATE + 1)
GROUP BY so_.zone_code, so_.cust_ship_to_id, FLOOR(SUBSTR(so_.end_time, 1, 2) / 8)

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jbancr1Author Commented:
sdstuber

I get a different error message this time.

ORA-01861: literal does not match format string:-1861
0
 
sdstuberCommented:
you must not be storing dates as dates.

what is end_date?  A string?  what is the format of it?

0
 
Jbancr1Author Commented:
FORMAT FOR END_DATE IS YYYY,MM,DD
0
 
sdstuberCommented:
I highly recommend you store dates as dates,

but, given your current setup,  try this...
  SELECT so_.zone_code,
         so_.cust_ship_to_id,
         FLOOR(SUBSTR(so_.end_time, 1, 2) / 8) end_time_shift,
         COUNT(so_.end_time)
    FROM csi.so_pick_detail so_
   WHERE so_.end_date = TO_DATE(SYSDATE, 'yyyy,mm,dd')
GROUP BY so_.zone_code, so_.cust_ship_to_id, FLOOR(SUBSTR(so_.end_time, 1, 2) / 8)

Open in new window

0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now