Feeds:
Posts
Comments

Archive for July, 2010

declare @passed varchar(max)

set @passed = 'FirstBit exec dbo.usp_Sites 4'


print substring(@passed, 1,  -- Change 1 to the vlaue you want the extraction to start from
		patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted
	)

print substring(@passed, 
		patindex('% exec %', @passed) + 1, 
		len(@passed) - patindex('% exec %', @passed)
	)

-- The function Left() and Right() are also used when you want the extraction to start from either 
-- left most or right most side. 
Advertisements

Read Full Post »

-- source http://searchsqlserver.techtarget.com/tip/Writing-T-SQL-functions-to-SQL-Server-system-schema

-- EXAMPLE 1

-- create a matematical function 
-- for example : solution for the parabolic equation
-- aX^2 + bX + c = 0
-- function gets A,B,C coefficients and the sign '+' or '-' to give
--  the first or second solution.
-- if  A=0 or no solution exists (B^2 < 4AC)  NULL  is returned

CREATE FUNCTION system_function_schema.fn_qsol 

      (@a real, @b  real, @c  real, @sign char(1))
    RETURNS real 
AS
BEGIN
    Declare @sgn real;                           
    IF ( ((@b * @b - 4*@a*@c) < 0) or (@a = 0) ) 
		RETURN (null)
    else
      begin
        if (@sign = '+') set @sgn = +1.0
        else set @sgn = -1.0
      end
      return ((-1 * @b + @sgn * sqrt (@b * @b - 4*@a*@c)) /  (2*@a))   
END
Go

--Example of a call to the function

-- Invoke the function
USE pubs
GO
SELECT fn_qsol (1,-1,-1,'+') , fn_qsol (1,-1,-1,'-')

Read Full Post »

-- Nulls cannot be counted whether by itself or even if its inside a calculation. Example below:

declare @c int
set @c = 0

declare @t table (a varchar(5), b int)

while (@c != 2)
begin
-- value will only be inserted in column 'b' so that in 'a' NUll is stored
INSERT into @t ( b )
select @c
set @c= @c + 1
end
select * from @t

select count(a) CountingNullsOnly , count(b) CountingNonNulls, count(a - b) CountingNullsInACalculation from @t

-- Here is a samll snippet to show if you keep forgetting which bit stand for which flag

declare @test bit

set @test = 'false'
select @test

set @test = 'true'
select @test

Read Full Post »

-- TSQL -- Fractions (or float) not being returnedTSQL -- Counting Nulls »TSQL, SSRS -- Date Manipulation
--July 19, 2010 by Usama 

SELECT getdate() Date1, getdate() + 1 Date2
     , DATEDIFF(day,GETDATE() + 1, getdate()) NegetiveFigure
	, DATEDIFF(day,GETDATE(), getdate() + 1) PositiveFigure_PlaceTheBiggerOnTheRight

Select dateadd(D, 0, datediff(D, 0, GETDATE())) -- Time Stripped (without any data type conversion for optimized performance)

Select DATEADD(m, DATEDIFF(m,0,GETDATE() )-1, 0) -- Beginning of previous month
select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) , 0)) -- End of previous month

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()) -1, "1900-01-01″) -- Beginning of previous month

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″)) -- End of previous month
*/

-----------------------------------------------------------------

Select DATEADD(m, DATEDIFF(m,0,GETDATE() ), 0) -- Beginning of the month

select DATEADD(ms,-3,DATEADD(m, DATEDIFF(m,0,GETDATE()) + 1, 0)) -- End of the month 

/*
SSRS 

=DateAdd("m",DateDiff("m", "1900-01-01″, Today()), "1900-01-01″) -- Beginning of the month 

=DateAdd("d", -1, DateAdd("m",DateDiff("m", "1900-01-01″, Today())+1, "1900-01-01″)) -- End of the month
*/ 

------------------------

-- Converting String to Datetime

declare @SelectedMonth int = 1, @year int = 2010 

-- Replace is not needed in the below line but is just illustrated if you want a leading 0 when the month is a single digit
declare @date datetime = convert(datetime, '28/' + replace(str(@SelectedMonth,2),' ‘,'0′) + ‘/' + str(@year ,4), 103)
select @date '


-------------------------------------- 

select 
DATENAME(weekday , getdate())
,  DATEADD(ms,-3,DATEADD(D, DATEDIFF(D,0,GETDATE()) , 0)) -- End of play > Yesterday 
, DATEADD(D, DATEDIFF(D,0,GETDATE())-1 , 0) -- Beginning of play > Yesterday 

Read Full Post »

In a table with 12 rows:

Select 100 / count(1)
from table

This would return an integer which is (8 which would be wrong if you need the fraction part).

To tell Sql Server to return your result as float you have to convert any one of the items in the calculation into float.

In this scenario there are two ways to work around this problem:
1) adding a decimal point after 100 ( 100. )
2) converting/casting the count(1) ( cast(count(1) as float) )

Read Full Post »