Get month value from Date in SQL server

Get month value from Date in SQL server:

Sample:


select month(getdate()) as month_num, datename(month,getdate()) as month_name


month_num                 month_name


----------- ------------------------------

5                          May





select month(getdate()) as month_num , datename(month,'1-jan-2013') as month_name

month_num                    month_name


----------- ------------------------------

5                            January

Read More

Error message 402: The data types ntext and varchar are incompatible in the equal to operator.

where clause with ntext in SQL server:

I have a column of type ntext in SQL server how can I evaluate it in a WHERE clause.


Structure of the Table:

sp_help Priorities


Coulumn_name           Type

---------------------------------------------------

PriorityId                      int

ProjectId                      int

Priority                         nvarchar

Description                   ntext



Sample :

select * from Priorities

where Description = 'High'



The error message for the above Query:

Msg 402, Level 16, State 1, Line 2

The data types ntext and varchar are incompatible in the equal to operator.



Solution:

select * from Priorities
where cast(Description as nvarchar(max)) = 'High'

Read More

INSERT Statement

INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

Syntax:

INSERT INTO table

(column-1, column-2, ... column-n)

VALUES

(value-1, value-2, ... value-n);



INSERT Statement - Using VALUES keyword example

Sample:

insert into customer (customer_id,customer_name)
values (101,'CustName');



SQL INSERT Statement - Using sub-selects

Sample:

insert into customer (customer_id,customer_name)
select cust_id,first_name from customer_temp;

Read More

CREATE TABLE Statement

The SQL CREATE TABLE statement allows you to create and define a table. Each column must have a data type. The column should either be defined as "null" or "not null" and if this value is left blank, the database assumes "null" as the default.




The syntax for the SQL CREATE TABLE statement is:

CREATE TABLE table_name

(
column1 datatype null/not null,
column2 datatype null/not null,
...
);



For Example



CREATE TABLE customer
(
customer_id number(10) not null,
customer_name varchar2(50) null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10)
);

Read More

SQL Statement Types

SQL Statement Types:

1. DDL – Data Definition Language.

2. DML – Data Manipulation Language.

3. DCL – Data Control languages



DDL Statements:

• Create – it used create objects like Tables,Views and etc…

• Alter – It used to alter the Structure of the Obejects like Tables, Views and etc…

• Truncate – It removes all the records in table.

• Grant – Provides / Assigns Privileges

DML Statements:

• Select – Retrieve data from tables.

• Insert – It helps to insert data in to table.

• Update – It helps to Modify the Existing data in table.

• Delete – It helps to Remove data from table.



DCL Statements:

• Commit – It will ends the current Transaction & making the changes to permanent.

• Savepoint – It will identify a point or Num of transaction in Set of Transactions to which we can later roll back.

• Rollback- It will undo / remove all the changes made by Current transaction.

Read More

SQL Introduction

SQL Introduction

SQL stands for Structured Query Language and it is generally referred to as SEQUEL. SQL was developed by IBM in the 1970s.

The American National standard Institute (ANSI) published first SQL stands in 1986 and released updates in 1992 & 1999, Known as SQL 92 and SQL99.

SQL is Simple & powerful language to Create, Access and Manipulate data and object structure in the data base.

The Below Topics will help us to learn SQL.

• SQL Statement Types
• CREATE TABLE Statement
• INSERT Statement
• How to extract Month, Year value from date using Extract Function in Oracle

To Learn More Concepts:

• Oracle SQL Blogs
• Oracle DBA
• Oracle Tuning
• Oracle Insight

Read More

Removing Leading Zeros

Removing Leading Zeros:

Sample data:

SQL> SELECT '0000100' AS Num FROM dual;

NUM
-------
0000100

Option 1:

SQL> SELECT To_Number('0000100') AS Num FROM dual;

       NUM
----------
       100

Option 2:

SQL> SELECT LTrim('0000100','0') AS Num FROM dual;

NUM
---
100

Read More