Feeds:
Posts
Comments

Posts Tagged ‘Split’

-- Please note that this is not my code and I have found it somewhere on the net

CREATE FUNCTION [dbo].[udf_Split] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(data NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (data)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Read Full Post »

-- SQL Server 2000/2005 (or 2008)
SELECT convert(CHAR(12), GETDATE(), 114) -- This is more widely used

SELECT DATEADD(Day, 0 - DATEDIFF(Day, 0, GetDate()), GetDate()) -- Retaining DateTime format

-- SQL Server 2008
SELECT CONVERT(TIME, GETDATE()) AS HourMinuteSecond
	,CONVERT(DATE, GETDATE(), 101) AS DateOnly

Read Full Post »

Use the conversion at your discretion.

A where clause having the convert keyword to convert date to varchar and compare would decrease the query time tremendously and you might need to do that when you want to split the time from date.

To test, I once ran a query containing a number of convertions and it ran for over 8hrs.
Amusingly, the same query ran for about 2.5 hours without the conversion.

--Example of such a query with the convert keyword:
select a, b
from tbl nolock
where convert(varchar(10), dateField, 121) &gt;= '2009-08-10'

--Optimizated/Alternative method 
declare @myDate datetime 
set @myDate = dateadd(day, 0 ,datediff(day,0, getdate()))
Select @myDate 

Similar Topics:
Find the end of month –
http://wp.me/pAchH-2t)

Read Full Post »