RE: [sqr-users] A "Between" Question

["Benzenhafer, Dave" <DBenzenhafer@houston.deepwater.com>]

You were correct. It had to do with the time added to the date. I found that
this works.

And trunc(wo.Entry_DATE) BETWEEN '01-JUL-03' and '03-JUL-03'

Thanks for your help.

Dave Benzenhafer 
dbenzenhafer@houston.deepwater.com 



-----Original Message-----
From: Wim Wolfs [mailto:Wim.Wolfs@mosa.nl] 
Sent: Friday, July 25, 2003 8:46 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] A "Between" Question


I'm not an expert in this field, but a guess:
1. Check the nls_date_format parameters (i.e. 'dd-mon-yy'')
2. Is the time part fooling you? 03-jul-03 23:59:59, 04-jul-03 00:00:01 etc.
3. Is between actually including total day of 01-jul-03 up to and including
03-jul-03 (01-jul-03 starts on 00:00:01 and 03-jul-03 ends on 23:59:59)

HTH,

W.Wolfs
Royal Mosa b.v.
Netherlands

> -----Oorspronkelijk bericht-----
> Van: Benzenhafer, Dave [mailto:DBenzenhafer@houston.deepwater.com]
> Verzonden: vrijdag 25 juli 2003 15:33
> Aan: sqr-users@sqrug.org
> Onderwerp: [sqr-users] A "Between" Question
> 
> I have two very similar queries that are looking at date fields in the >
same > table in an Oracle database using the BETWEEN predicate that returns
> different date results. > 
> The first statement returns data for dates July 1 and July 2. The second >
query returns data for dates July 1,2 and 3. I have confirmed that there >
is > data available for July 3 for the first query. > 
> The only difference between the two fields is that the wo.ENTRY_DATE is a
> required field and the wo.ACT_COMP_DATE  is not. > 
> Does anyone have any ideas why the BETWEEN in the two queries is acting so
> differently? > 
> Select
> wo.work_order_no,
> wo.ENTRY_DATE
> FROM EWM_WORK_ORDERS wo
> WHERE wo.DEPARTMENT_ID = 10432
>  And wo.Entry_DATE BETWEEN '01-JUL-03' and '03-JUL-03'
> 
> Select
> wo.work_order_no,
> wo.ACT_COMP_DATE
> 
> FROM EWM_WORK_ORDERS wo
> WHERE wo.DEPARTMENT_ID = 10432
> And wo.ACT_COMP_DATE BETWEEN '01-JUL-03' and '03-JUL-03'
> 
> 
> Thanks for your help.
> Dave Benzenhafer
> dbenzenhafer@houston.deepwater.com
> <mailto:dbenzenhafer@houston.deepwater.com>
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users