DECLARE@Stats TABLE ( SomeDate DATETIME )
INSERT @Stats SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM master..spt_values
DECLARE@Style INT
SET @Style = 100
Read more →
CREATEFUNCTION dbo.DATEDIFF360 ( @source DATETIME, @target DATETIME, @style BIT = 0 ) RETURNSINT AS BEGIN RETURNCASE @style -- European style WHEN 1 THENCASE WHEN DATEPART(DAY, @target) = 31 THEN 30 ELSE DATEPART(DAY, @target) END - CASE WHEN DATEPART(DAY, @source) = 31 THEN 30 ELSE DATEPART(DAY, @source) END -- US style ELSECASE WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAYOFYEAR, @source) = 60 AND DATEPART(MONTH, @source) = 2 THEN 30 WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAY, @source) < 30 THEN 31 WHEN DATEPART(DAY, @target) = 31 THEN 30 ELSE DATEPART(DAY, @target) END - CASE WHEN @source = DATEADD(MONTH, DATEDIFF(MONTH, -1, @source), -1) THEN 30 ELSE DATEPART(DAY, @source) END END + 30 * DATEDIFF(MONTH, @source, @target) END
Read more →
I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables. CREATEFUNCTION dbo.fnErf ( @z1 FLOAT, @z2 FLOAT, @MaxIterations TINYINT = 10 ) RETURNSFLOAT AS BEGIN IF @z1 IS NULL OR @z2 IS NULL RETURN NULL DECLARE @n TINYINT, @s1 FLOAT, @s2 FLOAT, @p1 FLOAT, @p2 FLOAT, @a1 FLOAT, @a2 FLOAT SELECT @n = 1, @p1 = 1, @p2 = 1, @a1 = @z1, @a2 = @z2, @MaxIterations = COALESCE(ABS(@MaxIterations), 10) WHILE @p1 <> 0.
Read more →
CREATEFUNCTION dbo.fnIsLeapYear ( @Year SMALLINT ) RETURNSBIT AS BEGIN RETURN CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229')) WHEN29 THEN 1 ELSE 0 END END
Legacy Comments
Brian Tkatch
2009-02-25
re: Fastest LeapYear checker?
Read more →
Some time ago, I wrote this article about how DATEDIFF works. http://www.sqlteam.com/article/datediff-function-demystified At the end I suggested two functions to calculate the number of months according to how human mind works.
Read more →
SELECT@@SERVERNAME AS SqlServerInstance, db.name AS DatabaseName, SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize, SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize, SUM(af.
Read more →
SELECTdb.name AS DatabaseName, bf.logical_name AS LogicalName, CASE bs.[type] WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Differential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' ELSE 'Unknown' END AS BackupType, CASE bf.
Read more →
SELECT d.theDate, DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend, 1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday FROM ( SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL SELECT '20081125' UNION ALL SELECT '20081126' UNION ALL SELECT '20081127' UNION ALL SELECT '20081128' UNION ALL SELECT '20081129' UNION ALL SELECT '20081130' )AS d
Legacy Comments
Uri Dimant
2009-01-14
re: Easy script for calculating weekday or weekend for a date Hi Peter.
Read more →
CREATEFUNCTION dbo.fnResolveFractionals ( @data VARCHAR(20) ) RETURNSFLOAT AS BEGIN RETURN CASE WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.
Read more →
I had a strange scenario today and I can't reproduce it. I changed current database to adventureworks and ran following code DECLARE@SQL NVARCHAR(200) SET@SQL = 'SELECT DB_NAME()' EXECsp_executesql@SQL EXEC (@SQL) The sp_executesql statement returned "master" and exec statement returned "adventureworks".
Read more →
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
Read more →
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished?
Read more →
-- Prepare sample data DECLARE@Sample TABLE ( ID INT, col INT ) INSERT@Sample SELECT0, 1 UNION ALL SELECT0, 1 UNION ALL SELECT0, 2 UNION ALL SELECT1, 1 UNION ALL SELECT1, 2 UNION ALL SELECT1, 3 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT2, 5 UNION ALL SELECT3, 6 UNION ALL SELECT3, 6 UNION ALL SELECT5, 8 UNION ALL SELECT5, 9 UNION ALL SELECT4, 7 -- Pivot the source data SELECTID, MIN(col) AS col1, CASE COUNT(*) WHEN 1 THEN NULL WHEN 2 THEN MAX(col) ELSE SUM(col) - MIN(col) - MAX(col) END AS col2, CASE COUNT(*) WHEN 3 THEN MAX(col) ELSE NULL END AS col3 FROM@Sample GROUPBY ID ORDERBY ID
Legacy Comments
dineshrajan
2010-08-10
re: Easy sorted numeric pivoting with maximum three columns Good Post.
Read more →
See his blog entry here http://www.sqlmag.com/Article/ArticleID/100884/sql_server_100884.html
And this older http://www.sqlmag.com/Article/ArticleID/95734/sql_server_95734.html
Read more →
With respect to my old algorithm found here http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx which is about how to sum up an unknown number of items, I have come up with a new algorithm. This new algorithm is about finding all possible sums and how many combinations you have of each sum.
Read more →
IFEXISTS(SELECT * FROM YourTable WHERE Number = 0) SELECT 0.0E ELSE SELECT CASE IsNegativeProduct WHEN 1 THEN -EXP(theSum) ELSE EXP(theSum) END FROM ( SELECT SUM(LOG(ABS(Number))) AS theSum, SUM(CASE WHEN Number < 0 THEN 1 ELSE 0 END) % 2 AS IsNegativeProduct FROM YourTable ) AS d
Read more →
SELECT* FROM::fn_dblog(DEFAULT, DEFAULT) AS l INNERJOIN sysobjects AS so ON so.name = l.[transaction name] SELECTso.name AS ObjectName, so.type AS ObjectType, MAX(CAST(l.[Begin Time] AS DATETIME)) AS LogTime FROM::fn_dblog(DEFAULT, DEFAULT) l innerjoin sysobjects so on so.
Read more →
DECLARE@Value NVARCHAR(200) SET@Value = 'a+(6aaaa02.......()/)(/)=(#¤NIOU#H¤CÖIÖERFÖNKER)123456789/fffffffffffff' WHILE@Value LIKE '%[^0-9]%' SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '') SELECT@Value
Legacy Comments
Uri Dimant
2008-11-12
re: Stripping out all non-numeric characters from a string declare @string varchar(200)
Read more →
SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly SELECTGETDATE() AS theFullDateTime, DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly
Legacy Comments
Bill Curnow
2008-11-12
re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime,
Read more →
Sometimes you face the situation where you have some items ranked, and someone always tries to bump the ranking by voting a "perfect 100" to a particular item. And when you rank the items with average function or root mean square, that single "perfect 100" vote still bumps the item at top of ranking.
Read more →