I am trying to make a calendar... still. And I am working on the week view of it. I need to query the table between two hours, but SQL can't just have a time... it needs a date and a time. So when you input just a time into the table MSSQL uses the date 1900-1-1. When you try to Query from CF using just a time it puts 1899-30-30. So is it possible to have the SQL completely disregard the date?

My code so far is:


I know that if I really wanted to is add a dateadd function but I am not entirely sure that CF will always uses 1899-30-30 as the date.

I don't know how you are getting 1899-30-30. But you are correct that CF uses a different epoch than MS SQL: 1899-12-30 versus 1900-01-01. Basically you are choices are to either pass in the right date value from CF, or let ms sql do the conversions.

I usually store the date _and_ time together, rather than separately. I have found it is easier to work with in most cases. (That is option #1), some other options are:

1. Store the date with the start/end time, then pass in the date _and_ time
<cfqueryparam value="#fullDateAndTime#" cfsqltype="cf_sql_timestamp">

2. Pass in a value with the same epoch as ms sql.
ie Create a CF date/time value using 1900-1-1 as the date

3. Pass in a string and let ms sql do the conversion
ie WHERE TimeColumn >= '07:00:00'

4. Possibly use ms sql's datePart functions
ie WHERE DatePart(h, TimeColumn) >= 7 AND ...


IMO #2 - 4 are a bit klunky and have some disavantages.

I got it working perfectly with datePart(). Rather than doing a BETWEEN statement I did an = one.



Now it returns everything on that hour.

Yes, unfortunately that usually kills any possibility of ms sql using an index on that column. Simplicity at the cost of performance. But if your database is small, it may not matter to you .

What if I queried the table, pulled everything by Username first and order it by starttime and then pulled stuff from that query based on the time... would that run faster?

No, the other method would still be faster. In large part because it would pull much less information ie 3 or 4 records as opposed to every record in the table.

This is more about optimization and writing queries that help the database perform more efficiently. The performance differences would be far less noticeable with a small database. But it is good to get into the habit of writing queries that scale well.

But I feel that if I first pull by the Username and the CurrentDate then pull from that by the StartTime it will run faster because the DB could have 1000 records but out of that 1000 records it will only need to pull the ones that are for that User and then the startdate bringing it down to maybe 10... if that.

Yes, retrieving 10 for a particular date would be quicker than pulling all 1000 records. But you are still pulling more information than you need. Plus you are doing this in a loop (which is not very efficient to begin with), so it all adds up. It is fine for one user, but add in many users and you end up putting a lot of unnecessary load on the database.

Really, it is better to let the database do the work and not try and out-think it. There is lot that goes on behind the scenes with the various database engines. It is a deep topic, and the behavior is not always intutive. I have done some reading on it, and found it is often not what I would expect at all, and it usually a lot more complicated than I thought.

But right now you are talking about a table with only 1000 records. So the chances are even a SELECT * would perform okay. Even if the query is not as efficient as it could be, databases are smarter and better at this. The best thing to do is try and write queries in a way that scales well, and let the database take it from there. Anything else should be handled by a dba, so you don't end up making things worse in an attempt to "fix" something.

In any case, you could still re-work the query so it doesn't use functions. That is one of the options I mentioned above.

Update To give you an idea of the complexity of this topic:
http://www.mssqltips.com/tip.asp?tip=1236
http://support.microsoft.com/kb/110352
http://blogs.msdn.com/queryoptteam/


This post has been edited by sansclue: 24 Apr, 2009 - 09:58 AM