Report between two dates not working correctly... 1

[kev510]

Hello everyone.
I am having some trouble with my .asp page.
The web page is designed to let the user select one date (LETS USE 1/1/2007), and it'll search for all data that were created on that date. The problem is that simply running the string below -

select * from TABLE where CreatedDate = '" & vSearchDate & "'

It will show only the data that were created on 1/1/2007 00:00:00.


I also tried
select * from TABLE where CreatedDate between '" & vSearchDate & "' and '" & vSearchDate & " 23:59:59'

and

select * from TABLE where CreatedDate between '" & vSearchDate & "' and DATEADD(day, 1, '" & vSearchDate & "')

but those strings will also show data that were created on 1/2/2007.

I need my page to show every data that were created on 1/1/2007 regardless of the TIME recorded in the data table.

Any suggestions are appreciated. Thank you so much!

 

[gmmastros]

This is, in my opinion, the best way to create this query. With the query written this way, if there is an index on CreatedDate, it will get used.

[tt][blue]
select * from TABLE where CreatedDate [!]>=[/!] '" & vSearchDate & "' and CreatedDate [!]<[/!] DATEADD(day, 1, '" & vSearchDate & "')[/blue][/tt]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kev510]

Hello George and thanks for your response!
I tried

select * from TABLE where CreatedDate >= '" & vSearchDate & "' and CreatedDate < DATEADD(day, 1, '" & vSearchDate & "')

with no results other than 1/1/2007 00:00:00 data... Any other suggestions?

 

[gmmastros]

ASP, right? Instead of executing the query, try a response.write followed by a response.end

This will display the query within the page. If the problem isn't obvious after doing that, then copy/paste the exact query here. Of course, if there is any sensitive data in the query, then replace the data with XXX.

You may also want to test this directly against the database (instead of going through ASP to do it) as a troubleshooting step.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Mercury2]

What about something like that ?

Select * from TABLE where CreatedDate = [b][COLOR=red]Date([/color][/b]'" & vSearchDate & ")'

 

[kev510]

Hello George.

Here's the string I got after doing response.write --

select * from sam_SERRule where EREntered >= '03/03/2006' and EREntered < DATEADD(day, 1, '03/03/2006') and StatusDetail = 1 order by ern

There are plenty of data where the data was created on 3/3/2006 -+ 1 day. The thing is that none of those data were created on 00:00:00, so I am not getting ANY results.

Mercury2, I tried your recommendation but no go. I tried convert(datetime, '" & vSearchDate & "') also.

 

[kev510]

Oh, and EREntered field indicates the date when the data was created...

 

[gmmastros]

Kev,

It appears as though you are using Microsoft SQL Server as your database. As such, you really should be posting this in the SQL Server Programming forum.

So you have the ability to connect to this database using Query Analyzer? If so, please connect with QA and run this query.

Select Top 20 *
From   sam_SERRule
Where  DateAdd(Day, DateDiff(Day, 0, EREntered), 0) = '20060303'

This part DateAdd(Day, DateDiff(Day, 0, EREntered), 0) will remove the time component from a DateTime field. Unfortunately, it will also cause your query to do a table scan, which is REALLY bad for performance.

I'd like you to run this query and then post the results here. You see, I'm wondering if the additional where clause And StatusDetail = 1 is causing your records to NOT return from the database.

Trust me when I say that the query, as written, will return data for the day if any records exist (regardless of time component) as long as it satisfies other filter criteria. I've used this method hundreds of times and it has never failed me. So... I'm thinking that there must be something else going on.

I hope this helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kev510]

That works beautifully!!
Sorry about posting in the wrong forum. I will make sure to post in the right forum next time.

In case you were wondering, it wasn't the "StatusDetail = 1" condition did not limit any of the results. It was just that the other solutions simply didn't work (they only retrieved the 00:00:00 data).

I wish there was a better way (in terms of performance) of truncating 00:00:00 from the date. But in my program, it should not matter because the table is not very big.

If you do find another way, please let me know. Thank you so much once again for your help!