A year ago I wrote post about SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice where I have discussed two different methods of getting datepart from datetime.
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
Method 2:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
I have summarized my post suggesting that either method works fine and I prefer to use Method 2. However, with additional tests and looking at SQL Server internals very carefully, I want to suggest that Method 1 is better in terms of performance. While running on GETDATE() both of the above functions are equally fast and efficient. However, when above query is ran on very large data table performance different is visible.
For testing purpose I have ran few queries where I am getting same result.
Run Following query 10 times with client statistics on ( Shift+Alter+S) in SSMS.
USE AdventureWorks
GO
SELECT DATEADD(d, 0, DATEDIFF(d, 0, StartDate))
FROM Production.WorkOrder
GO
Now run another query 10 times with client statistics on.
USE AdventureWorks
GO
SELECT CONVERT(VARCHAR(10),StartDate,111)
FROM Production.WorkOrder
GO
While looking at the average of statistics in it is clear that first query with DATEADD is much more efficient.
Let us compare both of the above script together and see the client statistics.
While comparing Numbers of Bytes sent across client and server as well client processing time, and total execution time in all case DATEADD is better. I suggest all my readers to use DATEADD when only DATEPART is required.
If you are using SQL Server 2008 it has new DATE datatype which does not have time part. I suggest to use DATE datatype or TIME datatype for users of SQL Server 2008. Read more SQL SERVER – 2008 – New DataTypes DATE and TIME.
If you want your date in any other format refer the UDF SQL SERVER – Get Date Time in Any Format – UDF – User Defined Functions.
Reference : Pinal Dave (https://blog.sqlauthority.com)
23 Comments. Leave new
Can you explain about switch and case statements
i need help on them
SELECT case when 1=1 then ‘yes’ else ‘No’ end
Hello,
I’m wondering whether use of FLOOR would be equivalent to your Method 1, both in how it’s executed by SQL Server, and thus in performance?
Method 1:
SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
FLOOR Method:
CAST( FLOOR( CAST( GETDATE() AS FLOAT ) ) AS DATETIME )
René
I think that floor method would take more time becuase of two functions floor and float
I Want to Become a good sql developer can you suggest me how to start Or what are the steps
i m storing the date and time in date_time field with same id like..
1. aman 7/29/2010 12:59:02 PM
1. aman 7/29/2010 1:05:31 PM
1. aman 7/29/2010 1:07:43 PM
how can i count the records using where condition
i m trying the following query but no any result:-
select Count(*) from tbname where date_time=’7/29/2010′
ouput is
0
where m doing mistake. please assist me.
thanks
vik
Make sure to read this post to know how to use date values effectively in the where clause
are you sure you using the correct date format in where condition.
First run the select and see the format of date and then you can use date >= ’29-07-2010′.
I am sure its the date format and your date should be in the format of dd-mm-yyyy instead of dd/mm/yyyy
thanks for reply Ashish…
your suggestion works for me but the problem is that after using date >= ’29-07-2010′ will also display the data who is greater than ’29-07-2010′ date but i need only which is equals to date=’29-07-2010′. well i am trying to display the daily transaction/history of the report. so i m still trying and hoping for solution. hope u understand my problem. :)
thanks regards
vik
It should be in unambiguous format like YYYYMMDD. Refer this post for more informations
thanks for reply.
in that case just use in your where condition as
where date >= ’29-07-2010′ and date < '30-07-2010'
or you can use between operator as well:-
like
where date between ’29-07-2010 00:00′ and ’29-07-2010 23:59:59′
You should always use unambiguous formats like YYYYMMDD format. The code you posted depends on the server’s date settings
We can simply use CONVERT(DATE, GETDATE()) right? It gives the date part of the datetime variable.
But note that this will work only from version 2008 onwards
I’m puzzled as to how a “solution” that depends on the date being so-many-characters – can even be considered. What if the locality and date format changes? Converting the date to a string format and using a substring to “chop off” the time, is what we called a “HACK” when people took pride in their work and formal education wasn’t considered a terrible thing.
I was wondering if using char(10) instead of varchar(10) would make any difference?
Not if the datalength is 10, otherwise CHAR will append leading spaces if there is lesser length
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
what is 111 in this query? Can you explain me.. I am not able to understand it.
i want to generate report that last 7 days how many bill are payed
using SQL server
soon i want to generate report by date daily,weekly and monthly
i am trying like this please help me
select count(bills) from table_name where date_column < sysdatetime();
but its result is total of all bils
i want to generete total bil of last few days
Hi,
I want to convert a datetime column in sqlite which is of format yyyy-mm-dd hh:mm:ss to seconds converted to 00 eg: 2014-12-27 16:46:59 to 2014-12-27 16:46:00
please help..
Thanks
Great Post!! It worked for me. Thank you.
I am glad that it helped you. Thanks for letting me know Aditi.