Most efficient way in SQL Server to get date from date+time?
I must admit I hadn't seen the floor-float conversion shown by Matt before. I had to test this out.
I tested a pure select (which will return Date and Time, and is not what we want), the reigning solution here (floor-float), a common 'naive' one mentioned here (stringconvert) and the one mentioned here that I was using (as I thought it was the fastest).
I tested the queries on a test-server MS SQL Server 2005 running on a Win 2003 SP2 Server with a Xeon 3GHz CPU running on max memory (32 bit, so that's about 3.5 Gb). It's night where I am so the machine is idling along at almost no load. I've got it all to myself.
Here's the log from my test-run selecting from a large table containing timestamps varying down to the millisecond level. This particular dataset includes dates ranging over 2.5 years. The table itself has over 130 million rows, so that's why I restrict to the top million.
SELECT TOP 1000000 CRETS FROM tblMeasureLogv2
SELECT TOP 1000000 CAST(FLOOR(CAST(CRETS AS FLOAT)) AS DATETIME) FROM tblMeasureLogv2
SELECT TOP 1000000 CONVERT(DATETIME, CONVERT(VARCHAR(10), CRETS, 120) , 120) FROM tblMeasureLogv2
SELECT TOP 1000000 DATEADD(DAY, DATEDIFF(DAY, 0, CRETS), 0) FROM tblMeasureLogv2
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 422 ms, elapsed time = 33803 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 625 ms, elapsed time = 33545 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 1953 ms, elapsed time = 33843 ms.
(1000000 row(s) affected) Table 'tblMeasureLogv2'. Scan count 1, logical reads 4752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 531 ms, elapsed time = 33440 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 1 ms.
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.
What are we seeing here?
Let's focus on the CPU time (we're looking at conversion), and we can see that we have the following numbers:
Pure-Select: 422
Floor-cast: 625
String-conv: 1953
DateAdd: 531
From this it looks to me like the DateAdd (at least in this particular case) is slightly faster than the floor-cast method.
Before you go there, I ran this test several times, with the order of the queries changed, same-ish results.
Is this something strange on my server, or what?
What is the most efficient way to extract a date from a timestamp in PostgreSQL?
To summarize the comments, your first and third solution are identical. Casting to a date simply uses the date
function according to @Nick Barnes.
Those options, plus option 2, requires a function to be run against every row of the table, so even if you have an index, it cannot be used.
Assuming there is an index on created_on
, this is your best bet:
select * from foo
where created_on >= '2014/1/1 00:00:00'
and created_on < '2014/1/2 00:00:00';
Most efficient way to compare datetime to varchar
Since this is a vendor application and you don't have control over the schema, you can refactor the WHERE clause
as below for a sargable expression. This will allow an index on somedateas
to be used efficiently. For example:
WHERE a.somedateas BETWEEN REPLACE(CONVERT(varchar(19), @begin, 120), '-', '_') AND REPLACE(CONVERT(varchar(19), @end, 120), '-', '_')
How to return only the Date from a SQL Server DateTime datatype
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
for example
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
gives me
2008-09-22 00:00:00.000
Pros:
- No varchar<->datetime conversions required
- No need to think about locale
Best approach to remove time part of datetime in SQL Server
Strictly, method a
is the least resource intensive:
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time?
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
- varchar is subject to language/dateformat issues
Example: Why is my CASE expression non-deterministic? - float relies on internal storage
- it extends to work out first day of month, tomorrow, etc by changing "0" base
Edit, Oct 2011
For SQL Server 2008+, you can CAST to date
i.e. CAST(getdate() AS date)
. Or just use date
datatype so no time
to remove.
Edit, Jan 2012
A worked example of how flexible this is: Need to calculate by rounded time or date figure in sql server
Edit, May 2012
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here Common SQL Programming Mistakes
Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but generally it will be a bad idea ...
Edit, Sep 2018, for datetime2
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
What is the fastest way to get only time part of datetime
Just cast/convert it to a time
, as that retain the correct typing (it's still a date and time data type):
SELECT CONVERT(time(0),datetimevalue), CAST(datetimevalue AS time(3))
As the value is a datetime
, the value will be accurate to 1/300th of a second, however, choose a precision appropriate for your data.
How to get Time from DateTime format in SQL?
SQL Server 2008:
SELECT cast(AttDate as time) [time]
FROM yourtable
Earlier versions:
SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable
Related Topics
How to List the Primary Key of a SQL Server Table
Performing SQL Queries on an Excel Table Within a Workbook With Vba Macro
Concatenate Multiple Result Rows of One Column into One, Group by Another Column
Return Multiple Columns of the Same Row as Json Array of Objects
Get the Week Start Date and Week End Date from Week Number
SQL Statement to Select All Rows from Previous Day
How to Create a Cron Job to Run an Postgres SQL Function
How Can Multiple Rows Be Concatenated into One in Oracle Without Creating a Stored Procedure
Is There a Lastindexof in SQL Server
Foreign Key Referring to Primary Keys Across Multiple Tables
How to Delete from Multiple Tables Using Inner Join in SQL Server