Working with dates and query result sets

Top (n) and order by

You SHOULD try to order result sets in the application layer. Because qualifying statements with ORDER BY are expensive in SQL!

You SHOULD NOT:-

Use SET ROWCOUNT. Prefer the TOP(N) syntax of the SELECT statement.
Use TOP in a SELECT list without using ORDER BY It's clear and returns consistent results.
Use constants in an ORDER BY clause; It's deprecated and makes code hard to read.

You SHOULD: -

Use a suitable date / time datatype. Their precision differs and affects the performance of your queries accordingly.
Express dates in ISO format to remove ambiguity. For example, 12/04/2017 may be interpreted as 12th April or 4th December depending on your configuration.

Examples of good practice

Use year, month day yyyymmdd format for a date alone (e.g., 20171204)

Use year, month, day and time format yyyy-MM-ddThh:mm:ss.xxx for a date & time (e.g. 2017-12-04T14:24:30.123)

Cast DATETIME or SMALLDATETIME fields to DATE if you're interested only in the date part. SQL Server supports this.

You SHOULD NOT:-

Use DATEDIFF when searching date ranges. As with other functions, this prevents the database engine making use of any indexes.
Use BETWEEN for searching datetime ranges. The start and end of the range can appear ambiguous. Use the operators >, <, >=, <=, = instead.
Use the + or -- operators on date/time fields; It is unclear what unit is being applied. For example, it's not clear that GETDATE()+1 will return this time tomorrow. Newer data types such as DATE, DATETIME2 and DATETIMEOFFSET throw errors when used in this way.