Calculating age of a person

In SQL Server:

You can use the built-in datediff function and compute it in years, however, you will run in to problems as it will round-up.

For example:

 

DECLARE @Birthdate DATE = '1920-08-31'

DECLARE @AgeasOfDate DATE = '2011-03-15'

 

SELECT DATEDIFF(yy,@Birthdate, @AgeasOFDate)

 

results in 91.

The person has not turned 91 yet. To address this we can calculate it as:

 

SELECT DATEDIFF(mm,@Birthdate, @AgeasOFDate)/12

This results in 90.

This is not completely accurate either... consider the example:

DECLARE @Birthdate DATE = '1920-08-31'

DECLARE @AgeasOfDate DATE = '2011-08-15'

 

SELECT DATEDIFF(mm,@Birthdate, @AgeasOFDate)/12 

results in 91, which is not correct. The person is still 90 years of age for another 16 days.

To address this, use the following:

SELECT  CASE WHEN DATEPART(day, @Birthdate) > DATEPART(day, @AgeAsOfDate)

             THEN DATEDIFF(month, @Birthdate, @AgeAsOfDate) - 1

             ELSE DATEDIFF(month, @Birthdate, @AgeAsOfDate)

        END / 12

 

 

In SSIS:

Setup a derived column transformation and use the following:

YEAR(AgeAsOfDate) - YEAR(BirthDate) - (SIGN(DATEPART("dy",BirthDate) - DATEPART("dy",AgeAsOfDate)) == 1 ? 1 : 0)

You ready for God Mode ???

Enable & Open God Mode on Windows

1. Right click on desktop to create a new Folder.

2. Name the folder as:

GodMode.{ED7BA470-8E54-465E-825C-99712043E01C}

3. You will see God Mode icon on the desktop. Double click it to open window as seen below.

Format datetime/date in MS SQL Server

Taken from http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)

– Oct 2 2008 11:01AM

SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2008

SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02

SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) – dd mon yyyy

SELECT convert(varchar, getdate(), 107) – mon dd, yyyy

SELECT convert(varchar, getdate(), 108) – hh:mm:ss

SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)

– Oct 2 2008 11:02:44:013AM

SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm

– 02 Oct 2008 11:02:07:577

SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm

– 2008-10-02T10:52:47.513

– SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd

SELECT convert(varchar(7), getdate(), 126) – yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy

Windows 7 Hotkeys

Taken from http://www.addictivetips.com/windows-tips/30-windows-7-hotkeys-ultimate-guide/

Apart from so many new features in Windows 7, Microsoft has also added new hotkeys to give individuals better control over their operating system. We have covered 30 hotkeys for Windows 7 which we believe everyone should know about. Master them all to increase your daily productivity.

Windows + Up Arrow

It is used to maximize the current window which is in focus, whether it is a windows explorer, a web browser, or any other application.

Windows + Left Arrow

Adjusts the current window to the left side and hence the window will cover almost half of the desktop space.

Windows + Right Arrow

It will adjust the current window to the right and thus it will cover half of the desktop space on the right.

Windows + Down Arrow

Minimizes the windows to the Task bar.

Windows + Space

All windows become transparent so you are able to see your desktop.

Windows + T

Shows the thumbnail preview of the running applications.

Windows + P

It is used to adjust the presentation options of the display on your computer or projector.

Windows + (+ / -) (Add/Minus)

It launches the Magnifier which can be used to zoom in/zoom out.

Shift + Click a Taskbar item

It opens a new instance of the particular application on which you clicked.

Windows + F

Windows + F is used to start the Windows Search.

Windows + X

It is used to run the Windows Mobility Center.

Windows + E

It is used to open the Windows Explorer.

Windows + [number]

It is used to run the pinned applications on the taskbar, Windows + 1 will run the first pinned application on the task bar, similarly Windows + 2 will run the second pinned application and so on.

Windows + Tab

It will launch the Windows Aero task switcher.

Windows + Pause

It is used to load the system properties window.

Ctrl + Windows + F

Search for computers if you are on a network.

Ctrl + Shift + Click on icon [Task Bar]

It is used to open a new instance of the particular application from the taskbar with administrator privileges.

Alt + D

It is used to select the address bar.

Alt + Win + [number]

Opens the jumplist for the applications which are pinned to the task bar.

Windows + B

It is used to shift the control to the System Tray icons one by one.

Shift + Right-click on a taskbar button

It is used to display the window menu for the specific taskbar application.

Ctrl + Shift + N

It is used to create a new folder in the windows explorer.

Alt + Up

Takes you up a folder level in the Windows Explorer.

Shift + Right-click on a file

It is used to add the Copy As Path option in the right click-menu of the file. This option copies the path of a file to the clipboard.

Shift + Right-click on a folder

It is used to add the command prompt option to the Folder, so you can easily open the command prompt in that particular folder.

Shift + Right-click on a grouped taskbar button

It is used to launch the windows menu for the group.

Ctrl + Click on a grouped taskbar button

It is used to cycle between the windows of the group.

Win + G

It is used to cycle between the Gadgets on the screen.

Ctrl + Esc

This hotkey is used to open the Start Menu.

Ctrl + Shift+Esc

It is used to directly open the Windows Task Manager.

-Aashish

SharePoint 2010 - Constrained Delegation

Think you have added all the SPN's and configured constrained delegation in your AD so that you can leverage Excel Services via SharePoint 2010 AND still get the dreaded User Credentials cannot be delegated error??

Well a quick glance at the SharePoint logs (C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\LOGS) shows the error as:

MossHost.TryGetWindowsIdentity: Failed to get WindowsIdentity from IClaimsIdentity. SPSecurityContext.GetWindowsIdentity() threw exception: System.InvalidOperationException: Could not retrieve a valid Windows identity.

Ah Ha! This is because the service account you have for the C2WTS (Claims To Windows Token Service) may have delegation setup BUT it must ALSO be part of the Local Administrators group on the application server on which it is running!

Add it to the Local Administrators group, reboot the server and voila!!

-Aashish

Fix for PDF Preview Handler for Vista

Finally found a fix for the crappy "PDF Preview Handler for Vista" error I kept getting when Outlook tried to preview attached PDF files in my email.

It is 64bit compatible ...Check out http://www.pretentiousname.com/adobe_pdf_x64_fix/index.html