Date part of date in MSSQL

I got a question the other about getting the date-only part of date/time stamp in SQL server. In my experience there are two ways to go about doing this. Both require casting data types from one type to another. The first example is how I used to go about performing this task before I understood how dates were represented numerically. The second method is related to my understanding of ColdFusion dates and timespans and fortunately transferred well to SQL.

First, the more verbose method. This requires breaking the date into its year, month, and day parts, putting them together in "yyyy/mm/dd" format, then casting that back to a date. This method involves 7 method calls including the final CAST(), not to mention string concatenation (which we all know is wicked slow).

CAST(
(
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( GETDATE() ) ) + '/' +
STR( DAY( GETDATE() ) )
)
AS DATETIME
)

The second method is much much cleaner. It uses only 3 method calls including the final CAST() and performs no string concatenation, which is an automatic plus. Furthermore, there are no huge type casts here. If you can imagine that Date/Time stamps can be represented, then converting from dates to numbers and back to dates is a fairly easy process (hopefully).

CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

In the example above, we are converting the date/time stamp to its float form. So today (when this was posted), this would yield something like 38903.745537114199. Days are not fractional, meaning that decimal places represent fractions of a day (in hours, minutes, seconds). Then, in order to get the days part (trim off the time portion of the date/time stamp) we are FLOOR()'ing the float value. This will give us the numeric representation of the DAY-only date. Then, we simply cast that back to DATETIME format and there you have it, a date-only date/time stamp.

If you want to compare, try running this:

SELECT
-- Get the full date/time stamp as a base.
(
GETDATE()
) AS date_time_part,

-- Trying casting to a string then back to a date.
(
CAST(
(
STR( YEAR( GETDATE() ) ) + '/' +
STR( MONTH( GETDATE() ) ) + '/' +
STR( DAY( GETDATE() ) )
)
AS DATETIME
)
) AS date_only_part,

-- Try casting to float, rounding, and back to date.
(
CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)
) AS date_only_part2,

-- Try casting just to float to see what it looks like.
(
CAST( GETDATE() AS FLOAT )
) AS float_value,

-- Try flooring to see the intermediary step.
(
FLOOR( CAST( GETDATE() AS FLOAT ) )
) AS int_value

As far as performance is concerned, there is no big surprise here. The second method has fewer function calls, no string concatenation, and in my opinion is a much more natural casting idea. In my testing on a table with several thousand records, the second method generally executed in a fraction of the time that string concatenation method executed.

On 3,000 rows (based on CFTimer execution time):

Average time of method one: 115ms

Average time of method two: 16ms (HUGE performance increase)

Additionally, in testing, method one has some runs that were all over the place; very large execution times. Method two, on the other hand, consisently performed at the same speed, give or take a few ms.

Commaseperated value to Row using XML

CREATE FUNCTION [dbo].[SplitString]
(
@delimited nvarchar(max)
) RETURNS @t TABLE
(
ID numeric(18,0) identity(1,1),
Item nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @delimited = replace(@delimited,',','.')
set @xml = N'' + replace(@delimited,'.','') + ''

insert into @t(Item)
select
r.value('.','varchar(5)') as item
from @xml.nodes('//root/r') as records(r)

RETURN

end

Please see image for proper code, here some code missing due to html.
Sorry for the inconvenience.