Date and Time SQL Quandry (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

pd_it_guy -> Date and Time SQL Quandry (4/3/2008 17:31:51)

Just when I thought I understood something, then this:

Every record we are tasked to display has an occurrence date, (occ_date), and occurrence time, (occ_time). Dates are standard; times run from 1, which is 1 second past midnight, to 240000, which is midnight.

If we only cared about the date, irrespective of time, no problem. Thus:
WHERE (c.occ_date >= '03/25/2008' AND c.occ_date <='04/01/2008')

BUT now the boss wants only those records for events BETWEEN 03/25/2008 at 09:00:00 and 04/01/08 at 12:56:00.

Clearly, each record really now needs the derived equivalent of Microsoft time, where you have the serial number with the decimal portion for time, so we know if it should be included or not. And of course the user selection option needs to be in the same format. So far, I have found no single VB conversion function to do this and every VB return somehow comes back in a reader friendly format, which is not what we wanted.

Does anyone know if this can een be done?



rdouglass -> RE: Date and Time SQL Quandry (4/4/2008 13:29:47)

Must be the week for dates. [:D]

Anyways, you can get the decimal equivalents but I think I'd use the DatePart VBScript function to get the seconds from any time you send at it. However, this script I'm using assumes 24 hour time formats.

I'd use DatePart like this. We can get both the hours and minutes from date part like this:

<%=datepart("h",Now())%> and <%=datepart("n",Now())%>

and we can combine them with some multiplication to get the total number of minutes like this:

<%=(datepart("h",Now())*60) + datepart("n",Now())%>

and then multiply that sum by 60 to get the seconds:

<%=(datepart("h",Now())*60) + datepart("n",Now())*60%>

You can replace "Now()" with whatever date/time variable you're using; request.form or whatever.

So now it just depends on whether you're looking above or below that number (before or after that time). That's more challenging I'm sure you'll agree. And to me how you approach that depends on a few factors.

The big factor for me would be to consider how many records you expect to be returned. If you think there will be less than 1000 or so, I'd search first for just all date range matches and dump them into an array.

Once they were in the array, I'd then loop thru them checking the date fields for matches for the start date. If the start dates match and the seconds are less than the one we calculated from above, than discard the record. If it's greater than, then keep the record.

Do a similar thing for the end dates but keep the records that have seconds less than the calculated amount. BTW. You will probably have different calculated seconds for each unless you went from the same times.

Does that make sense? That's how I'd look at it anyways.

How comfortable are you with arrays? [8|] [;)]



pd_it_guy -> RE: Date and Time SQL Quandry (4/4/2008 18:16:40)

First thanks so much (once again) for the thoughtful and thorough response. Since the post, I came upon several possibilities, one being, the mktime() function, but I cannot seem to get it to work in the asp environment, iand perhaps it is a totally wrong function, or incorrect usage of it.

My plan was as follows:

See if I can find some function I can feed days, years, months, hours, minutes to, to get a serial number. From the discussion, it looks like DatePart would do. Make sure if I bump the second by just one it is slightly bigger, thus comparable.

Next, see how we would fold this into a query. Looks like we are going to have to dig out the very same thing for every record, and set a variable or series of variables to it. This is where it gets troublesome, as the user input can be controlled and actually preset; making it work in SQL another matter. But I can save that battle for later.

And no, I am not up on arrays. Maybe putting in the time on DatePart this weekend makes sense for now. And that time, that ranges from 1 to 240000 is going to be a challenge.

Let me read up on and work with DatePart and report back. For something so simple??? it is proving to be quite a challenge.



pd_it_guy -> RE: Date and Time SQL Quandry (4/5/2008 11:45:06)

Maybe I am making this whole thing way too complicated.

if start_date and start_time and end_date and end_time are the choices, and each record has the attribute of occ_date and occ_time, then all I really need to be able to do is something like the following

This is where start and end dates encompass 3 separate dates.

GIVE ME THE RECORDS,
WHERE, (among a lot of other things),
IF occ_date=start_date, THEN
ALL THE RECORDS, WHERE occ_date=start_date AND occ_time>=start time
ELSEIF occ_date>start date AND occ_date<end_date THEN
ALL THE RECORDS, regardless of time
ELSEIF occ_date=end_date
ALL THE RECORDS, WHERE occ_date=end_date AND occ_time<=end_time
END IF

Can this string of conditions be buried in an SQL statement; does it tolerate IF -THEN -ELSE's. And it would have to test each record against this sting of conditions. Since start and end dont necessarily have to be 3 separate days I guess we could first test the spread of start and end dates and have it branch to separate blocks. What I am asking is if this approach is even feasible.



pd_it_guy -> RE: Date and Time SQL Quandry (4/6/2008 13:05:46)

Sometimes, an imperfect solution but a solution none the less when up against a hard deadline from an impatient boss is better than none at all. This morning I just discovered that I can run successive iterations of the database results region all on the same page; each seems to execute properly, and all the results neatly append to each other. No apparent decrease in retrieval speed.

With that, I did the following:

First pass through gets the records WHERE occ_date=start_date AND occ_time>=start_time
Second pass through gets records WHERE occ_date>start_date AND occ_date<end_date
Last pass through gets records WHERE occ_date=end_date AND occ_time<=end_time

Still testing to make sure I did not miss something. So far as I have found, each is a mutually exclusive, so there are no repeats and for other than the first return set we set the no records message to ""

Now I have a bit more time to learn how to do this right- script all of this in at one shot.



pd_it_guy -> RE: Date and Time SQL Quandry (4/6/2008 13:58:29)

I kept at it and am now testing this- it seems to work... but... it just can't be this simple can it. I hope the disjoins at the OR's are right where we are assuming if ANY one of the 3 lines returns TRUE, it is a TRUE, and I get the record. Hard dates and times to be replaced by user selects from lead off page- don't see any problems there. Maybe we have this now. Comments, upgrades, ideas welcome otherwise we just go with this for now...

fp_sQry="SELECT c.primary_key, c.occ_date, c.occ_time, c.final_case_type," _
& " c.zone, ct.translation, ct.priority, c.address, c.report_flag, c.remarks, c.clear_remarks," _
& " NVL(o.officer_name,'NO OFFICER ASSIGNED') AS officer_name" _
& " FROM case_type ct, cc_data c LEFT OUTER JOIN officer_number o" _
& " ON c.reporting_officer1 = o.badge" _
& " WHERE c.occ_date='04/01/2008' AND c.occ_time>='090000' AND c.final_case_type=ct.code" _
& " OR c.occ_date>'04/01/2008' AND c.occ_date<'04/03/2008' AND c.final_case_type=ct.code" _
& " OR c.occ_date='04/03/2008' AND c.occ_time<'190000' AND c.final_case_type=ct.code" _
& " ORDER BY c.occ_date ASC, c.occ_time ASC"



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
4.699707E-02