Sql Server Date Query

I am new to development and want to know the professional way to deal with dates in SQL Server. In my applications mainly we deal with the DATE datatype and no concern with time pa

Solution 1:

SQL handles dates just fine, so you do not need to convert the dates.

If you pass in the parameters as date types, then you will have no problem:

CREATEPROCEDURE myProc 
    @startdate, 
    @enddateASSELECT emp_id, emp_name, join_date
    FROM EmployeeTable
    WHERE join_date BETWEENstartANDend;

Solution 2:

Unless you want to format a date in your output in a specific way, there's no reason to convert the date to a varchar. You're using the date datatype, so let SQL do the comparisons for you.

If you want to compare dates in a date range, you can use this:

WHERE join_date BETWEEN'2010-01-01'AND'2010-12-31'

Solution 3:

Keep dates as dates. Do not convert it to strings. That is unnecessary. When you send dates in to SQL Server from your code, do it with parameters, then you don't have to worry about the right format in your strings.

SQL Server Date data types:

• Date: 0001-01-01 through 9999-12-31
• SmallDateTime: 1900-01-01 through 2079-06-06 (Accuracy 1 minute)
• DateTime: January 1, 1753, through December 31, 9999 (Accuracy millisecond)
• DateTime2: 0001-01-01 through 9999-12-31 (Accuracy 100 nanoseconds)

Solution 4:

It's a minor point but worth noting that all queries presented to SQL Server are in TEXT. At some stage, based on some language and translation setting in the data access layer (OLEDB, Native, ADO) it gets turned into a textual form, so dates are always presented as "text".

The best format to use is always YYYYMMDD for SQL Server. Even YYYY-MM-DD can be wrong, for obscure dateformat settings. See this example.

set dateformat dmy  -- more than common for non-US locationsselectCONVERT(varchar, convert(datetime, '2010-12-31'), 112)

It fails.

Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type toa datetime data type resulted in an out-of-range value.

That covers the format to use when you have to construct the date embedded in the SQL statement. When possible however, please parameterize queries for benefits like

1. prevention of SQL injection
2. letting the db connectivity layer ensure the right formats when generating the TSQL
3. query plan re-use on the SQL Server
4. point 3 = better performing queries and more efficient SQL Server