Get Month Name from SQL

Hi

try this example to get month name from SQL Server

SELECT DATENAME(MONTH,’08/15/1947′)

where date should be in format MM/dd/yyyy

Hope this helps

Good Luck

Easy SQL If Record Exists, Update It. If Not, Insert It.

Hi

try this example :-

CREATE PROCEDURE dbo.spAddUser 
(    @UserID AS int,
     @FirstName AS varchar(50),
     @LastName AS varchar(50)    
)
AS
     BEGIN
          DECLARE @rc int
            UPDATE [Users]           
     SET FirstName = @FirstName, LastName = @LastName
           WHERE UserID = @UserID    
      /* how many rows were affected? */
         SELECT @rc = @@ROWCOUNT 
           IF @rc = 0 
             BEGIN    
               INSERT INTO [Users]                              
  (FirstName, LastName)                        
  VALUES(@FirstName, LastName)           
  END           
 END

Hope this helps

Good Luck

Renaming a Stored Procedure

Hi

try this :-

Syntax:
sp_rename ‘procedure_name1′, ‘procedure_name2′
procedure_name1
The current name of the stored procedure
procedure_name2
The new name of the stored procedure.
A stored procedure can be renamed. The new name should follow the rules for identifiers.
Examples
Code:
EXEC sp_rename ’spGetAvgGrade’, ’spNewAvgGrade’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures.
The object was renamed to ’spNewAvgGrade’.
Explanation:
In the above example we change the name of the stored procedure spGetAvgGrade to spNewAvgGrade

Hope this helps

Good Luck

Get Month Name from SQL

Hi

try this SQL query to get month name

SELECT ID,datename(month,PostDate) as Month FROM yourTable

where PostDate is field in my table of datetime type

Good Luck

Insert Null Value in DateTime Column SqlServer 2005 using asp.net

Hi

try this example:

steps:

1) Create Simple web application (asp.net with C#).

2) create below table in the database. here i made table (table_1 in test database).

DataBase Name :Test
Table Name : Table_1
ColumnName       ColumnDataType
id               int (Auto Increment & Primary Key)
code             varchar(50) (Allow Null True)
date             datetime (Allow Null True)
 3) put the below control on the form.3.1) Calendar (id –> Calendar1)

3.2) Button (id –> btnInsert)

3.3) Button (id –> btnGet)

3.4) Label (id –> lblDataValue)

4) put the below code in to C# page.

Add below name space first :

using System.Data.SqlClient;
copy the below code and check. //connection string
    //please change as per your server and database
    string constr = @”Data Source=(local);Initial Catalog=test;Integrated Security=True”;
    public int insertedId
    {
        get { return Convert.ToInt32(ViewState["inserttedid"]); }
        set { ViewState["inserttedid"] = value; }
    }
    protected void btnInsert_Click(object sender, EventArgs e)
    {
        //if user is not selected any value from calendar at that time null is inserted
        //otherwise selected date is inserted
        string datetime = Calendar1.SelectedDate.Date.Equals(DateTime.MinValue) == true ? “Null” : “‘” + Calendar1.SelectedDate + “‘”;
        string strins = “insert into table_1 values ” +
            “(‘amit’,” + datetime + “);select scope_identity();”;
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strins, sqlcon);
        sqlcon.Open();
        //get the id of inserted row using Scope_Identity function
         insertedId = Convert.ToInt32(cmd.ExecuteScalar());
        sqlcon.Close();
    }
    protected void btnGet_Click(object sender, EventArgs e)
    {
        //get the inserted record
        string strSelect = “Select * from table_1 where id =” + insertedId;
        SqlConnection sqlcon = new SqlConnection(constr);
        SqlCommand cmd = new SqlCommand(strSelect, sqlcon);
        sqlcon.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            //set the value in the label for display purpose 
            lblDataValue.Text = dr["date"] == System.DBNull.Value ? DateTime.MinValue.ToShortDateString() : Convert.ToString(dr["date"]);
        }
        sqlcon.Close();
    }
Thanks.

Display age from Birthday

Hi

try this SQL Query to display age from Birthday

SELECT BirthDate, DATEDIFF(YYYY,BirthDate,GETDATE()) – CASE WHEN 100 * MONTH(BirthDate)+DAY(BirthDate) < 100 * MONTH(GETDATE())+DAY(GETDATE()) THEN 0 ELSE 1 END AS Age FROM [Some Table]

Hope it helps

Good Luck

Select top and last five record from Database

In Query try this:

FOR TOP 5
select top 5 * from table

//——————-

FOR LAST 5
select top 5 * from table order by primarykey_columnname desc

Good Luck

Copy Table from Database to another

Hi

try this example:

—————————————————-
Method : 1
—————————————————-

USE PropertyIdea
GO
—-Create Table
CREATE TABLE Country (CountryID int, Country nvarchar(50),Code int)

—-INSERT INTO Country using SELECT
INSERT INTO Country (CountryID , Country ,Code)
SELECT CountryID , Country ,Code
FROM [Kunj.Corp].[dbo].[Country]

—-Verify that Data in Country
SELECT * FROM Country

—————————————————-
Method : 2
—————————————————-
USE PropertyIdea
GO

—-Create Table

CREATE TABLE Country (CountryID
int, Country nvarchar(50),Code int)

—INSERT INTO Country using SELECT

INSERT INTO Country (CountryID, Country ,Code)

SELECT CountryID , Country ,Code

FROM [Kunj.Corp].[dbo].[Country]

—-Verify that Data in Country

SELECT * FROM Country

Good Luck