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
WHERE
CASE
  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

Advertisements

Random sampling with sample frequency in SQL

<pre>

<code>

–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
as
(

SELECT 1 as num
UNION ALL
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
 ORDER BY NEWID()
 — a.link,a.img,b.num

</code>

</pre>


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)

https://jingyangli.wordpress.com/2010/12/02/dnn-sercurity-setting-locked-issue-dnn-5-5-0-and-dnn-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!)

 

use  
myDB
GO 

–Edit Ribbon show for all users
–Remove the entry
–Created by Jingyang Li 01/07/2011 
 
DELETE FROM
 dbo.TabPermission
WHEREpermissionId=4 and RoleId=-1
Go
   
–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
 
GO
 
 

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:

 <code>

EXEC
 

 

 sp_MSForEachDB

 
 

 

 

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

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

end

 

 

</code>