Between Dates...and times

[MCubitt]

When using BETWEEN against date values where the date is time specific (ie not just 00.00.00) rows are excluded for the end date if they are timed past midnight (ie 00.00.01 and onwards).

Please see specific example with "Good" and "Bad" situations:

GOOD:

select creation_date from ifsapp.person_info_tab
where creation_date between to_date('&Start_Date','DD/MM/YYYY')
and to_date('&End_Date','DD/MM/YYYY')
for 19/07/2004 and 20/07/2004

Creation_Date
2004-07-19-00.00.00
2004-07-19-00.00.00
2004-07-19-00.00.00
2004-07-19-00.00.00
2004-07-20-00.00.00
2004-07-20-00.00.00


BAD:

select dated from ifsapp.inventory_transaction_hist2
where dated between to_date('&Start_Date','DD/MM/YYYY')
and to_date('&End_Date','DD/MM/YYYY')
for 19/07/2004 and 20/07/2004

Dated
2004-07-19-06.10.01
2004-07-19-06.10.02
2004-07-19-06.10.02
2004-07-19-06.10.03
2004-07-19-06.10.03
2004-07-19-06.10.03
2004-07-19-06.10.03
2004-07-19-06.10.03
2004-07-19-06.15.34
.
.
.
2004-07-19-22.08.41
2004-07-19-22.08.41
2004-07-19-22.08.41
2004-07-19-22.08.43
2004-07-19-22.08.43
2004-07-19-22.08.43
2004-07-19-22.08.43


What is the suggested method for dealing with this, can one code the SQL to default the todate to "23:59:59" ?


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[r937]

your sig is really annoying



there are many methods for dealing with this

note that A BETWEEN B AND C
equates to A >= B AND A <= C

my preference is to "open-end" the upper end, and use the next day's date

so if i were looking for all the datetime values for 2004-08-10 and 2004-08-11, i would use midnight of the 12th as the upper end

i.e.
where enddate >= '2004-08-10'
and enddate < '2004-08-12'

rudy

http://r937.com/

SQL Consulting

 

[MCubitt]

r937,

Thanks but that solution would include rows on teh extra day which have midnight so should be excluded. I guess the tochar and using || '235959' will probably have to do!

Thanks anyway (and sorry for the annoying of true sig!)







There's no need for sarcastic replies, we've not all been this sad for that long!

 

[r937]

that solution would include rows on teh extra day which have midnight"

no, it wouldn't



rudy

http://r937.com/

SQL Consulting

 

[MCubitt]

Sorry, I should read the fact you used >= and < rather than BETWEEN!

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

I missed a "trunc call" so should have used:

select dated from ifsapp.inventory_transaction_hist2
where trunc(dated) between to_date('&Start_Date','DD/MM/YYYY')
and to_date('&End_Date','DD/MM/YYYY')
for 19/07/2004 and 20/07/2004

Which works well!

Thanks

There's no need for sarcastic replies, we've not all been this sad for that long!

 

[jimbopalmer]

In Oracle, most of my packages have a constant defined

g_almost_a_day := 86399/86400

then you can use
...
and l_this_date between t_start_date and t_end_date + g_almost_a_day;

there are 86400 seconds in a day, the 86399th one is at 23:59:59

I tried to remain child-like, all I acheived was childish.