SELECT [Now], BinaryFormat, SUBSTRING(BinaryFormat, 1, 4) AS DayPart, SUBSTRING(BinaryFormat, 5, 4) AS TimePart, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days], DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today], CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks], DATEADD(MILLISECOND, 1000.
Read more →
DECLARE @Year SMALLINT = 2011, @NumberOfYears TINYINT = 3 ;WITHcteCalendar(FirstOfMonth, LastOfMonth) AS ( SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth, DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth FROM master.
Read more →
Or ask yourself, "How much of the database has changed since last backup?". Here is a simple script that will tell you how much (in percent) have changed in the database since last backup.
Read more →
CREATEFUNCTION dbo.fnGetEasterDate ( @Year SMALLINT ) RETURNSDATE AS BEGIN RETURN ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6) FROM ( SELECT CASE @Year % 19 WHEN 0 THEN '0415' WHEN 1 THEN '0404' WHEN 2 THEN '0324' WHEN 3 THEN '0412' WHEN 4 THEN '0401' WHEN 5 THEN '0419' WHEN 6 THEN '0409' WHEN 7 THEN '0329' WHEN 8 THEN '0417' WHEN 9 THEN '0406' WHEN 10 THEN '0326' WHEN 11 THEN '0414' WHEN 12 THEN '0403' WHEN 13 THEN '0323' WHEN 14 THEN '0411' WHEN 15 THEN '0331' WHEN 16 THEN '0418' WHEN 17 THEN '0408' WHEN 18 THEN '0328' ELSE NULL END WHERE @Year BETWEEN 1900 AND 9999 )AS d(BaseDate) ) END
Legacy Comments
iStan
2011-01-17
re: Fast easter day function Hi,
Read more →
will happen in just 4 days. The child part is taken care of. Multiple times. Now it's time for the married part.
Me and my girlfriend Jennie will be married in Bjuv church at 3pm, Saturday the 28th of August 2010.
Read more →
First create a table type like this
CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL ) GO
And then you create a function like this CREATE FUNCTION dbo.
Read more →
The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation. Today, I wanted to see how often a collision (or false positive) can occur.
Read more →
This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant. I've come up with a solution that need only 125 bytes of storage.
Read more →
Today, let's examine encoding with SQL Server and XML datatype. DECLARE@Inf XML SET@Inf = '<?xml version="1.0" encoding="utf-16"?> <root> <names> <name>test</name> </names> <names> <name>test1</name> </names> </root> ' SELECTx.
Read more →
This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog. Consider this sample data DECLARE@Sample TABLE
Read more →
I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data.
Read more →
I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem.
Read more →
This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE@Sample TABLE ( RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Expense SMALLMONEY NOT NULL ) -- Populate sample data INSERT@Sample ( Expense ) VALUES (12.
Read more →
When I try to rename a node name such as a table or column in Management Studio, I cannot use DELETE key to remove previous characters. However, I can use BACKSPACE key.
Read more →
As some of you know, I was awarded Microsoft Most Valuable Professional (MVP) for SQL Server in July last year. Now it's time to see if I get my MVP renewed, or if I lose the award.
Read more →
Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity.
Read more →
SELECTu.name, l.dbname FROMsys.sysusers AS u INNERJOIN sys.syslogins AS l ON l.sid = u.sid
Read more →
-- Setup user supplied parameters DECLARE @WantedTable SYSNAME SET@WantedTable = 'Sales.factSalesDetail' -- Wanted table is "parent table" SELECT PARSENAME(@WantedTable, 2) AS ParentSchemaName, PARSENAME(@WantedTable, 1) AS ParentTableName, cp.Name AS ParentColumnName, OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName, OBJECT_NAME(parent_object_id) AS ChildTableName, cc.
Read more →
Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.
Read more →
SELECT name AS Setting, CASE WHEN @@OPTIONS & number = number THEN 'ON' ELSE 'OFF' END AS Value FROMmaster..spt_values WHEREtype = 'SOP' AND number > 0 Or this SELECT* FROMsys.
Read more →