Have you ever had a time when you were working with an ICriteria to return a specific group of data and found that it just wasn't returning what you wanted and you weren't sure why. In the past when I've had to troubleshoot troublesome SQL queries I would open up SQL Server Profiler to see what might be happening behind the scenes that could be messing up what is being returned. This can be a little bit troublesome when dealing with NHibernate. NHibernate generates the SQL in such a fashion that it becomes rather difficult and time consuming to make these queries readable after looking at their output withing SQL Profiler. Here is an example of a query from profiler, generated by NHibernate:

SELECT this_.id as id49_2_, this_.part_number as part2_49_2_, this_.quantity as quantity49_2_, this_.manufacturer as manufact4_49_2_, this_.part_name as part5_49_2_, this_.description as descript6_49_2_, this_.requested_date as requested7_49_2_, this_.notes as notes49_2_, this_.status as status49_2_, this_.cancelled_date as cancelled10_49_2_, this_.location_user_id as location11_49_2_, this_.customer_id as customer12_49_2_, locationus3_.person_id as person1_40_0_, locationus3_.name_first as name2_40_0_, locationus3_.name_middle as name3_40_0_, locationus3_.name_last as name4_40_0_, locationus3_.name_suffix as name5_40_0_, locationus3_.phone_number as phone6_40_0_, locationus3_.fax_number as fax7_40_0_, locationus3_.email_address as email8_40_0_, locationus3_.job_title as job9_40_0_, locationus3_1_.password as password41_0_, locationus3_1_.is_active as is3_41_0_, locationus3_1_.username as username41_0_, locationus3_1_.location_id as location5_41_0_, locationus3_1_.shopping_cart_id as shopping6_41_0_, customer1_.customer_id as customer1_72_1_, customer1_.name as name72_1_, customer1_.created_by as created3_72_1_, customer1_.dt_created as dt4_72_1_, customer1_.po_prefix as po5_72_1_, customer1_.is_active as is6_72_1_, customer1_.po_required as po7_72_1_, customer1_.enforce_order_multiples as enforce8_72_1_, customer1_.order_alert_recipient_id as order9_72_1_, customer1_.order_alert_dollar_amount as order10_72_1_, customer1_.show_on_hand as show11_72_1_, customer1_.is_central_billing as is12_72_1_, customer1_.display_promatch_number as display13_72_1_, customer1_.site_id as site14_72_1_ FROM quote_requests this_ left outer join people locationus3_ on this_.location_user_id=locationus3_.person_id left outer join dbs_users locationus3_1_ on locationus3_.person_id=locationus3_1_.person_id inner join customers customer1_ on this_.customer_id=customer1_.customer_id WHERE customer1_.site_id = @p0 and (this_.status = @p1 or (this_.status = @p2 and this_.cancelled_date < @p3)) and this_.status = @p4

My question was, what in the world is this query actually doing? In comes Instant SQL Formatter. Once you past the above generated SQL into Instant SQL Formatter and press 'Format SQL' it gives you this:

SELECT this_.id                             AS id49_2_,
       this_.part_number                    AS part2_49_2_,
       this_.quantity                       AS quantity49_2_,
       this_.manufacturer                   AS manufact4_49_2_,
       this_.part_name                      AS part5_49_2_,
       this_.DESCRIPTION                    AS descript6_49_2_,
       this_.requested_date                 AS requested7_49_2_,
       this_.notes                          AS notes49_2_,
       this_.status                         AS status49_2_,
       this_.cancelled_date                 AS cancelled10_49_2_,
       this_.location_user_id               AS location11_49_2_,
       this_.customer_id                    AS customer12_49_2_,
       locationus3_.person_id               AS person1_40_0_,
       locationus3_.name_first              AS name2_40_0_,
       locationus3_.name_middle             AS name3_40_0_,
       locationus3_.name_last               AS name4_40_0_,
       locationus3_.name_suffix             AS name5_40_0_,
       locationus3_.phone_number            AS phone6_40_0_,
       locationus3_.fax_number              AS fax7_40_0_,
       locationus3_.email_address           AS email8_40_0_,
       locationus3_.job_title               AS job9_40_0_,
       locationus3_1_.password              AS password41_0_,
       locationus3_1_.is_active             AS is3_41_0_,
       locationus3_1_.username              AS username41_0_,
       locationus3_1_.location_id           AS location5_41_0_,
       locationus3_1_.shopping_cart_id      AS shopping6_41_0_,
       customer1_.customer_id               AS customer1_72_1_,
       customer1_.name                      AS name72_1_,
       customer1_.created_by                AS created3_72_1_,
       customer1_.dt_created                AS dt4_72_1_,
       customer1_.po_prefix                 AS po5_72_1_,
       customer1_.is_active                 AS is6_72_1_,
       customer1_.po_required               AS po7_72_1_,
       customer1_.enforce_order_multiples   AS enforce8_72_1_,
       customer1_.order_alert_recipient_id  AS order9_72_1_,
       customer1_.order_alert_dollar_amount AS order10_72_1_,
       customer1_.show_on_hand              AS show11_72_1_,
       customer1_.is_central_billing        AS is12_72_1_,
       customer1_.display_promatch_number   AS display13_72_1_,
       customer1_.site_id                   AS site14_72_1_
FROM   quote_requests this_
       LEFT OUTER JOIN people locationus3_
         ON this_.location_user_id = locationus3_.person_id
       LEFT OUTER JOIN dbs_users locationus3_1_
         ON locationus3_.person_id = locationus3_1_.person_id
       INNER JOIN customers customer1_
         ON this_.customer_id = customer1_.customer_id
WHERE  customer1_.site_id = @p0
       AND (this_.status = @p1
             OR (this_.status = @p2
                 AND this_.cancelled_date < @p3))
       AND this_.status = @p4

Then, all you have to do is DECLARE your parameters, set their values and run the query and view the output.

 This is going to save me a ton of time during development and troubleshooting troublesome queries.

Recently, while trying to mash Team Foundation Server and SQL Server Reporting Services, I needed to convert a DateTime in SQL to a short date string. This is how I did it.

CONVERT(VARCHAR, ClosedDate, 101)

That's it. The definition for doing so is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

MSDN has quite an article describing CONVERT in depth.