Saturday, December 8, 2012

How to Find Leap Year in sql server 2012

How to Find Leap Year in sql server 2012



How to determine whether a year is a leap year

To determine whether a year is a leap year, follow these steps:
  1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
  2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.
  3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
  4. The year is a leap year (it has 366 days).
  5. The year is not a leap year (it has 365 days).

Here is the code for Finding LEAP YEAR in SQL SERVER



declare @chkyear int = 2011;

if @chkyear % 4 = 0
begin
  if @chkyear % 100 =0
   begin
         if @chkyear % 400 = 0
          Print CAST(@chkyear as varchar(4)) + N' is a Leap year';
   end
   else Print CAST(@chkyear as varchar(4)) + N' is a Leap year';
end
else print CAST(@chkyear as varchar(4)) + N' is not a Leap year';
 
Tags:Determine leap year , check for Leap year,Detecting a Leap Year in sql server 2012,Function to determine a leap year.

Add Primary Key Constraint to Existing table in sql server


Add Primary Key Constraint to Existing table in sql server 

Syntax:
              alter table [table] add constraint [constraint name] primary key(columns)


for ex:  alter table tb add constraint PK_const_ primary key(ID).
 Here table called tb adding primary key constraint name PK_const_  for ID column













Tags:How to Add Primary key  in sql server 2012, How to add primary key to existing table in sql server,
How to add primary key constraint in sql server,How to add primary key constraint to existing table in sql server,

Add default value to existing column in sql server

 Add default value to existing column in sql server

Syntax
alter table [table name] add  default(value)  for [column name] 

Ex:  alter table tb add  default(0)  for status
 here status column is bit column,  adding default value as false.



Tags:Add DEFAULT to existing column - Database ...,

Change DEFAULT value for a column in sql server, Alter Table set default value ,

SQL Server Default Column Values,

Add Column Default Value - SQL Server

How to Add two columns in Sql Server

How to Add two columns in existing table in Sql Server with default values


SQL-Server allows to add null value columns to an existing table. not null value columns also can be added if table is empty. If data exists, truncate/copy to some other table, then add not null value columns.

Here is the example for adding 2 columns in exiting table in sql server 2012


ALTER TABLE CUSTOMERS ADD [JOINED ON] DATETIME default getutcdate(),
status bit default 0;

The above alter statement add 2 columns to an existing table with default values utc date and status is false.


tags:How to Add two columns in Sql Server with default column constraint to table,How to Add columns to existing table in Sql Server ,How to Add new columns in existing table in Sql Server,sql-server 2012, SQL-Server:How to add column with default constraint values, How to add datetime column to table









ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'firstname' cannot be added to non-empty table 'cUSTOMERS' because it does not satisfy these conditions.









How to Add two columns in Sql Server with default column constraint to table,How to Add columns to existing table in Sql Server ,How to Add new columns in existing table in Sql Server,sql-server 2012, SQL-Server:How to add column with default constraint values