Convert SQL Server to Optimal Short Date

Question

What is the best short date for converting to SQL Sever for use in a predicate.

I have a date 2013-06-11 15:06:27.000 and you want to use the short date 2013-06-11 in the predicate.

What would be the best short date for converting to SQL Server for this purpose?

MSDN - Date Conversion

Answer 1

For SQL2005 +:

Note: Select Convert(DateTime, Convert(VarChar, DateTimeColumn, 12)) <operator> <const> does not work SARG ! So, if you have an index on a DateTimeColumn , then SQL Server cannot search ( Index Seek ) in this column. Instead, SQL Server will use Index Scan , Clustered Index Scan or Table Scan .

If you want to filter rows in a DATETIME column, you can use the predicates DateTimeColumn >= RangeStart AND DateTimeColumn < RangeEnd or DateTimeColumn BETWEEN RangeStart AND RangeEnd .

How can I generate RangeStart and RangeEnd ?

  DECLARE @SelectedDate DATETIME; SET @SelectedDate='2013-06-11 15:06:27.000'; SELECT DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate),0) AS Start, DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate)+1,0) AS [End 1], DATEADD(MILLISECOND,-3,DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate)+1,0)) AS [End 2] 

Note 2 For a DATETIME column, the last millisecond can be one of these {0,3,7} (see BOL ).

Results:

 Start End 1 End 2 ----------------------- ----------------------- ----------------------- 2013-06-11 00:00:00.000 2013-06-12 00:00:00.000 2013-06-11 23:59:59.997 

Example # 1:

 ... WHERE h.OrderDate>=DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate),0) AND h.OrderDate<DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate)+1,0) 

Example # 2:

 ... WHERE h.OrderDate>=DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate),0) AND h.OrderDate<=DATEADD(MILLISECOND,-3,DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate)+1,0)) 

Example # 3:

 ... WHERE h.OrderDate BETWEEN DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate),0) AND DATEADD(MILLISECOND,-3,DATEADD(DAY,DATEDIFF(DAY,0,@SelectedDate)+1,0)) 

Answer 2

 Select Convert(DateTime, Convert(VarChar, GetDate(), 12)) 

Answer 3

not really that hard if you use sqlserver 2008 +

 cast(@date as date) 

or

 convert(date, @date) 

Since you are using sqlserver 2005

 CONVERT(CHAR(10), @date, 121) 

However, if you intend to compare with another date, keep the date and time format:

 dateadd(day, 0, datediff(day, 0, @date)) 

Answer 4

How about this?

 SELECT CONVERT(CHAR(12),GETDATE(),12) 

Answer 5

If you are using a version of SQL Server prior to 2008, I would use the following method:

 SELECT DateAdd(dd, DateDiff(dd, 0, Current_Timestamp), 0); 

The reason I chose this method compared to the others is because it avoids casting to the character data type (and vice versa).

You can also use this method for versions after 2008 inclusive, depending on what you intend to do with this value ... If you intend to compare it with a different datetime value, this method will least save the same data type for comparison . If we compare the value of a date , then the aforementioned SELECT Cast(Current_Timestamp As date);