SQL: Find people’s birthday which is today or a date range

SELECT DATEADD(day, DATEDIFF(day,0,GETDATE())-1,0) as StartOfYesterday

SELECT DATEADD(day, DATEDIFF(day,0,GETDATE()),0) as StartOfToday

SELECT DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0) as StartOfNextday

SELECT * FROM students
  WHEN Dateadd(YEAR, Datediff(YEAR, dob, Getdate()), dob) > Dateadd(DAY, Datediff(DAY, 0, Getdate()), 0) THEN Dateadd(YEAR, Datediff(YEAR, dob, Getdate()), dob)
  ELSE Dateadd(YEAR, Datediff(YEAR, dob, Getdate()) + 1, dob)
END BETWEEN — StartOfToday
Dateadd(DAY, Datediff(DAY, 0, Getdate()) , 0) AND
— StartOfNextday
Dateadd(DAY, Datediff(DAY, 0, Getdate()) + 1, 0)

–Refer Michael Valentine Jones:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70975

Random sampling with sample frequency in SQL



–create table images ( id int identity(1,1), link varchar(200),img varchar(max), Freq int)
–insert into images values (‘aaa’,’blob1′, 20)
–insert into images values (‘bbb’,’blob2′, 60)
–insert into images values (‘ccc’,’blob3′, 40)
–insert into images values (‘zzz’,’blob4′, 0)

;WITH mycte

SELECT 1 as num
SELECT num+1 as num from mycte WHERE num<100


select top 10 b.num, a.link, a.img from mycte b, images a
 WHERE a.Freq>=b.num
 — a.link,a.img,b.num



DNN Permission Issues with Child Portal in DNN 5.5.+ (up to 5.6.0) and a Fix

I wrote a post last month to fix the permission issue after a child portal is created with DNN 5.5+ (upto 5.6.0)


There is another issue related to the permission during the child portal creation. I have an updated script to fix the issue with the DotNetNuke control panel shows for all users in the child portal.

Here is the script:

(Before try the script, Backup your DNN database!)



–Edit Ribbon show for all users
–Remove the entry
–Created by Jingyang Li 01/07/2011 
WHEREpermissionId=4 and RoleId=-1
–Fix Edit function after child portal created
–Created by Jingyang Li 12/2/2010
UPDATE  p  SET p.AdministratorRoleId=d.AdministratorRoleId,
 p.RegisteredRoleId =d.RegisteredRoleId FROM ( SELECT PortalID, [Administrators] as AdministratorRoleId, [Registered Users] as RegisteredRoleId  
FROM (SELECT PortalID, RoleID, RoleName from roles ) src
 PIVOT  (MIN(RoleID) FOR RoleName IN ([Administrators],[Registered Users]))pvt
  INNER JOIN  dbo.Portals p ON d.PortalID=p.PortalID

How to set default value for Time(7) data type in SQL Server 2008

You can set a default value for time7 data type from  SSMS: (’00:00:00′) as the default value.

Or use this query:

ALTER TABLE  yourTable ADD  CONSTRAINT [DF_time7Column]  DEFAULT ('00:00:00.0000000') FOR [time7Column]

How to modify Copyright statement for all DNN instances

Chris Hammond blogged a way to setup copyright statement for all pages on a DNN site from an Admin setting through the use of  SkinObject on a skin. You can read the blog at this link: http://www.dotnetnuke.com/Resources/Blogs/tabid/825/EntryId/2933/Updating-your-DotNetNuke-Copyright-Statement-for-2011-and-beyond.aspx

You can simply leave the Copyright setting blank from the Admin page, the DNN Framework will pick current year along with PortalTitle as your Copyright statement.

However, I have a need to update over 200 DNN sites on our server with my company  copyright statement: Here is a quick code snippet to do so:








‘ IF EXISTS (select 1 from [?].information_schema.tables wheretable_name=”PortalLocalization”)

declare @sql_updateFooterText Nvarchar(2000)

SELECT @sql_updateFooterText = N” UPDATE [?].dbo.PortalLocalization SET FooterText=””Copyright 2011 by Marsh U.S. Consumer”””

–EXEC sp_executesql @sql_updateFooterText

print @sql_updateFooterText