Just had a quick issue that was driving me a bit nuts. I have a function that inserts site stats into a table. I was querying the table in Query Analyzer and was wondering why SQL server was incorrectly sorting my results. My query looked something like this:
select top 10 foo, dateAdded
from tbl
order by dateAdded desc
Which I fully expected to give me the 10 most recent records. In fact it was working fine before lunch today. Well after lunch it started getting weird on me. It was returning noon, then 11 am, then 10 am and then 1 pm! What? 1pm should be first! I took another look at my insert query and found this:
<cfqueryparam value="#dateformat(now(), "mm/dd/yy")# #timeformat(now(), "hh:mm")#" cfsqltype="cf_sql_timestamp">
Aha! In my timeformat() I was using the 12 hour clock. SQL server wants the 24 hour clock!
So a quick change to this (note HH):
<cfqueryparam value="#dateformat(now(), "mm/dd/yy")# #timeformat(now(), "HH:mm")#" cfsqltype="cf_sql_timestamp">
And the sorting was back to normal.
Update: Of cource I could have also just passed now() like so:
<cfqueryparam value="#now()#" cfsqltype="cf_sql_timestamp">
