Jump to content
Changes to the Jaspersoft community edition download ×

how can my sql specify a comparison between timestamp and a date in the select statement?


Recommended Posts

XML source shows:

<field name="due_dt" class="java.sql.Date"/>

<field name="filed_ts" class="java.sql.Timestamp"/>

<![CDATA[select fe.filer_event_id, fe.filer_info_id, fe.filer_event_cd, fe.report_type_cd, fe.form_type_cd, fe.applicable_year, 
fe.period_start_dt, fe.period_end_dt, fe.due_dt, r.report_info_id, r.filed_ts, r.report_type_cd, r.form_type_cd, 
r.period_start_dt, r.period_end_dt, r.source_category_cd, f.filer_ident 
from filer_event fe
inner join filer_info f on fe.filer_info_id=f.filer_info_id
inner join filer_event_report fer  on fe.filer_event_id=fer.filer_event_id
inner join report_info r on fer.report_info_id=r.report_info_id
where r.filed_ts is not null
and r.form_type_cd=$P{FilerType}
and r.filed_ts >fe.due_dt
and (fe.due_dt between to_date ($P{StartDate},'yyyy-mm-dd') and to_date ($P{EndDate},'yyyy-mm-dd') )
and r.source_category_cd='ELECTRONIC'
and r.report_type_cd not like '%EXCEED%' 
and r.report_type_cd not like '%A10%'
and fe.filer_event_id not in (
    select fe.filer_event_id from filer_event fe
    inner join filer_event_document fed on fe.filer_event_id=fed.filer_event_id
    inner join document_info d on fed.document_info_id=d.document_info_id
    where d.document_cd='LATEA'
    and (fe.due_dt between to_date ($P{StartDate},'yyyy-mm-dd') and to_date ($P{EndDate},'yyyy-mm-dd') ))
order by f.filer_ident]]>
This select means that r.filed_ts = 2017-01-17 15:15:15 when due_dt is 2017-01-17 turns up late.  I want it such that any filed_ts up  to 2017-01-17 24:60:59 is timely.  So if I strip the timestamp from filed_ts so that I compare date to date, it would work.  But I can't figure out how to do that solely within the select statement. 
Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...