Yesterday I hade the unfortenate task to change the database collation for a particular database. Not only the default database collation should be changed, but also all columns! After some investigating about how to do this, I noticed that check constraints and foreign key constraints must be removed before changing a collation.
Read more →
Today I was involved in an interesting topic about how to check if a text string really is integer or not. This is what I finally suggested. CREATEFUNCTION dbo.fnIsINT ( @Data NVARCHAR(11) ) RETURNSINT AS BEGIN RETURN CASE WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN @Data IN('-', '+') THEN NULL WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL ELSE CAST(@Data AS INT) END END
Read more →
It has come to my attention that sometimes there are more than 100,000 records for which a "running streak" should be calculated on, so my previous blog post http://weblogs.sqlteam.com/peterl/archive/2009/08/12/Another-running-streaks-algorithm.aspx will not do.
Read more →
It has been some years since this article was posted http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data and things has evolved since then. So I thought about using XML to solve the case. If the number of records are large, maybe this approach http://weblogs.
Read more →
This is a simple query for creating a script for your foreign keys in your database. It may need tweaking for composite keys. If that's the case, see here how to concatenate http://www.
Read more →
In my previous blog post about how to manipulate data in XML columns, http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx, I didn't have time to include how to delete elements. Now I have and here you can see how to delete elements from a XML column.
Read more →
This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2.
Read more →
Here is a simple base converter that manages [from and to] base 2 to 16. CREATEFUNCTION dbo.fnBaseConvert ( @Value VARCHAR(8), @FromBase TINYINT, @ToBase TINYINT ) RETURNSVARCHAR(32) AS BEGIN RETURN ( SELECT SUBSTRING('0123456789abcdef', 1 +(x.
Read more →
Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative. Well, today I have. Recently I blogged here http://weblogs.
Read more →
Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year. This code below calculates the number of weekdays for any given period.
Read more →
Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATEFUNCTION dbo.fnMonthWeekDays ( @Year SMALLINT, @Month TINYINT ) RETURNSTINYINT AS BEGIN RETURN ( SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5) FROM ( SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt WHERE ISDATE(10000 * @Year + 100 * @Month + 31) = 1 UNION ALL SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2) WHERE ISDATE(10000 * @Year + 100 * @Month + 30) = 1 UNION ALL SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29) WHEN 1 THEN DATEADD(MONTH, 12 * @Year - 22800 + @Month, -3) ELSE '18991231' END ) AS d WHERE @Year BETWEEN 1753 AND 9999 AND@Month BETWEEN 1 AND 12 ) END
Legacy Comments
Sowmya
2009-10-22
re: How to calculate the number of weekdays in a month Can u please explain the logic ?
Read more →
This function calculates the number of weeksdays in a year, and has error-checking for invalid years. It is also language independant. Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period.
Read more →
It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year. So how to convert JDEdwards to DATETIME?
Read more →
Some time ago, I displayed how to work with XML data when searching for data stored in a XML column. Here Some XML search approaches and here Updated XML search (test case with variables).
Read more →
Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year.
Read more →
You call this function with a date. The function returns a table with one record and 3 columns. First column is Weekday; Monday = 1, Tuesday = 2, Wednesday = 3; Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7.
Read more →
You call this function with three parameters: 1. Any date of the month in question 2. The weekday to calculate; Monday 1, Tuesday 2, Wednesday 3, Thursday 4, Friday 5, Saturday 6 and Sunday 7 3.
Read more →
I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler.
Read more →
For some time ago, I posted an algorithm how to get the XML structure automatically. Today I stumbled across another approach which seems to be faster. Reservations though I haven't tested this against large xml data yet.
Read more →
CREATEFUNCTION dbo.fnCheckDanSSN ( @SSN CHAR(10) ) RETURNSBIT AS BEGIN IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' RETURN 0 DECLARE @x CHAR(6) SET @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2) IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '57' AND ISDATE('20' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '58' AND '99' AND ISDATE('18' + @x) = 0 RETURN 0 DECLARE @Digits INT SET @Digits = 4 * SUBSTRING(@SSN, 1, 1) + 3 * SUBSTRING(@SSN, 2, 2) + 2 * SUBSTRING(@SSN, 3, 2) + 7 * SUBSTRING(@SSN, 4, 2) + 6 * SUBSTRING(@SSN, 5, 2) + 5 * SUBSTRING(@SSN, 6, 2) + 4 * SUBSTRING(@SSN, 7, 2) + 3 * SUBSTRING(@SSN, 8, 2) + 2 * SUBSTRING(@SSN, 9, 2) + 1 * SUBSTRING(@SSN, 10, 2) RETURN1 - SIGN(@Digits % 11) END
Legacy Comments
Saggi Neumann
2009-05-30
re: How to check Danish personal number This is how we validate ID numbers in Israel.
Read more →