All posts by Basavaraj Biradar

How to set default database in Sql Server Mangement Studio. A Time Saving Tip

Sql Server, Tips/Tricks

In SSMS whenever we connect to any database server by default the MASTER database is the default selected one. Which forces us  in changing the database the one we want work using the USE statement or by using mouse and changing the selected db from the drop-down list.This is were anoying, and till recently daily I was wasting time in doing this multiple times.

If we know that by default most of the time we will be connecting to a specific database related to our project, then Sql Server Management Studio (SSMS) provides a way to change this. And next time onwards this nerw selected database will selected by default instead of the MASTER DB.

Steps: 1) Open Sql Server Management Studio
 2) Go to object Explorer -> Security -> Logins
 3) Right click on the login and select properties
 4) And in the properties window change the default database and click OK.

Alternatively, we can change this by below statement aswell:

Exec sp_defaultdb@loginame='TestUser', @defdb='Test'

Note: Please don’t experiment this on production database and also User should have access to the database to which we are changing.

Please correct me, if my understanding is wrong. Comments are always welcome.

How to Calculate Age in Sql Server

Scripts, Sql Server, Tips/Tricks,

Recently, while filling some form needed to provide my age in years. Thought of calculating it in sql server and as usual used the DATEDIFF function like below, but it gave me wrong answer:

Script which gives Wrong Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth',
     GETDATE() 'Current Date',
 DATEDIFF(YEAR,@DOB,GETDATE()) 'Age in Years'

Incorrect Result:

Date of Birth   Current Date                Age in Years
-------------   -----------------------     ------------
1980-12-29      2012-04-26 09:46:21.833     32
(1 row(s) affected)

Answer returned by the above query is 32 years which is 1 year more than the actual age. Reason for this mistake is, we are taking into consideration just the year part and not the day and month. So, we will get 1 year extra if the birth day has not yet crossed in the current year. So it means the above query will return correct age in years if the birthday has already passed in the current year.

To solve this issue we need to subtract 1 year if the birth date has not yet crossed in the current year:

Script which gives Correct Answer:

DECLARE @DOB DATETIME ='12/29/1980'
SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',
   DATEDIFF(YEAR,@DOB,GETDATE())
     -
   (CASE
      WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB)
            >  GETDATE() THEN 1
      ELSE 0 END)
   'Age in Years'

Correct Result:

Date of Birth   Current Date              Age in Years
-------------   -----------------------   ------------
1980-12-29      2012-04-26 10:23:05.690   31
(1 row(s) affected)

Please correct me, if this is not the correct way of calculating the age. Comments are always welcome.

Simple Quiz On Sql Server Select statement

Sql Server, Tips/Tricks, ,

If we execute a statement like below, it will throw error:

SELECT xyz

Result:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'xyz'.

Can you guess what the below statement execution will result and reason for the same? Be frank don’t execute the statement and answer. By the way Today, I came to know about this behaviour only after executing this statement 🙂

SELECT 111xyz