Trying to get a date field to correctly filter from a parametr 2

[bobpv]

Greetings all,

I am really at a loss here, seemed like it should be simple, but up against a brick wall.
Running a SQL stored procedure (SQL 2000)with several unions for a Crystal Report. Due to the large amount of data in the database, I am filtering the info in each union with an IN statement to reduce the data which is displayed to Crystal, like this

Where JOb# in
(select JOB# from Header Table
where header.jobdate >= @startparameter
and header.Jobdate >= @endparm
from header)
This only brings over jobs related to the various other tables which fit in the range. Each table used in the union can have many records for the same job.

The parameters are char(10), and are passing fine from Crystal. Issue is with the time field in the Job date (it is a date time field) when the app assigns a job date, it also assigns a time, which is causing a problem when you run the report for the end date. The passed parameter information comes over as 01/10/2009 (working fine!) except for that darn time portion in the SQL data, it excludes all jobs from the end parameter for the date (01/15/2009 for example won't show, as it is got a time which is making it greater that the cutoff). I tried several cast, converts and datediffs, but am having no luck with this one. Maybe been looking too long at it, but hope someone has an idea as to how to get the datetime to not see the time, but still read the 10 character parameter being passed from Crystal. I would much prefer to NOT use a datetime in CR, due to the end users skill levels.

Many thanks in advance.

 

[markros]

To return next day from the passed @EndParm and so use <@EndParm in query:

set @EndParm = DATEADD(day, DATEDIFF(day, '20040101', @EndParm) + 1, '20040101')

 

[markros]

See also thread183-1575641

 

[PWise]

[s]Where JOb# in
(select JOB# from Header Table
where header.jobdate >= @startparameter
and header.Jobdate >= @endparm
from header)[/s]

try

inner join [Header Table]
on [Header Table].[JOB#]=[Deatail Table].[JOB#]
and [Header Table].jobdate Between @startparameter and dateadd(s,86399
,@endparm)

 

[bobpv]

pwise, that dateadd worked. I guess it was the old 'forest for the trees' syndrome, I was not thinking about that use for dateadd.

Many thanks, I am much obliged.

 

[markros]

IMHO, pwise solution may not be 100% accurate.

Check also

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/series-of-bad-habits-to-kick-by-aaron-be

and start from the top (to my shame that's the only one I read so far).

 

[bobpv]

markros,

it seems to be working just fine for my needs, it is allowing the items that were entered with a date of (example) 2009-01-16 12:31:000 ( yes I know my format on the date time field is wrong!but just an example for discussion))to be displayed when CR passes the parameter of <=01/16/2009 to the query. I was not able to get these results when using the date add function on the day, as it brought in also jobs entered on the 17th (add 1 day to selection of jobs).

Lots of data to verify, but so far so good. I did appreciate you reply and also the link - it was VERY helpful.

Can you expand on why it may not be 100%? Thanks in advance...

 

[markros]

I believe the reason of not 100% accurate should be explained in the latest blog I posted. It's because of how dates are stored and if there are some dates that occur on the last second before midnight of the next day you may miss these dates.

So, to be 100% safe it's better to use < than tomorrow's date 12 midnight.