How to find out day and year and month in respective date in sql server .2005 | sql server 2008.

we will see how can we find out day and month and year in given date separately .
to find out same we have to use DAY ,MONTH and YEAR keyword respectively which is by default or defined by sql server .
so no need to put some extra efforts.
just write same keyword after you use that keyword they will appear in pink colour in sql server.
lets look at examples.
suppose i have last date of submission my fee now i want to find  out day and month and year of lastdateofsubmission then i will
write like this.

for  day select DAY(LastSubmissionDate) from tableName
for month select MONTH(LastSubmissionDate) from tableName
for year  select Year(LastSubmissionDate) from tableName


now output will be
31 --day
2  --month
2014 --year


we can write in same line also..
select   DAY((LastSubmissionDate) ),MONTH ((LastSubmissionDate) ),YEAR ((LastSubmissionDate)  ) from tableNamewhere DueDate <> '' and DAY((LastSubmissionDate) ) in ('1','13','30')
which will gives you date which comes under day 1 , 13, 30 in every month and year.

Sql command set | DML(Data Manipulation language) | Insert | Update | Delete

What is DML?
"Sql command set".
1-> "DML(Data Manipulation language)".
DML includes select ,insert , update , delete .
Let's study all there functionality one by one.

Database names:Stud
Table Name:student

i> Select;
we use select to fetch data from table or we can say to get record from table
use stud
select * from student.
here we can apply condition also like using where clause.
select * from student  where id=234

ii>Insert :
to insert  new record  in respected table we use insert.
insert into student values ("enter records you want to insert").


iii>Update 
to done modification in existing record.
update table table_name set column_name name where clause.

iv>Delete:
to delete unwanted records from table.
delete from table_name where column_name =parameter;

sql command set | DDL(Data definition language). | create | Alter | Drop

"Sql command set".
1-> "DDL(Data definition language)".
DDL includes Create ,alter , drop,truncate , comment , rename.
Let's study all there functionality one by one.

i->Create
 Create used  to create new table and object.
or you can create many tables and objects.
examples.
to Create table.
Create table student
(
id int identity primary key,
name varchar(20),
roll_no int
)
ii->Alter
 Used  to modify structure of table and other object..
alter table student alter name varchar(100)
using this query we can change size of column .
alter table student add  fee int.

iii->drop
Drop is used to drop table or column of table or object.
alter table student drop column name.
drop table student.
drop database stud.
we can drop database also by using drop keyword or command.


iv->Truncate 
Truncate use to delete all data present in the table.
But not table structure means , table will be there but it won't show any of record.

Truncate table Table_name
Truncate table student.

v->Comments
to add comments in procedure.

sp_insertrecord --'1','abcval'


vi->Rename
to rename  single table or multiple table in same database.
to rename single or multiple column in same table.

sp_rename 'existtablename' ,'renamedtablename'
SP_RENAME 'student.roll_no' ,'roll'  --to rename column name

SP_RENAME 'student' ,'stud'

sql server 2005/2008 | primary key | Identity key | Auto increment

how to set identity by query in table .
while creating by query in sql server 2008-2005 if you want to add
primary key with identity then you need to add primary key with identity at same time.
or if  you have already added then alter that  column but need to take care of  it.
here i have shown how to add identity with primary key in sql server 2005 and sql server 2008.
db name is stud
and table name is student.

user stud
create table student
(
id int NOT NULL primary key identity ,
roll int not null,
name varchar(30) not null

)

how to write Prime number logic in sql server 2008

In this program i am going to give you code how to write prime number in sql server 2008.
prime number divisible by itself and by one.that we call as prime number.
i have shown here using while and if loop to check whether given number is prime or not

ALTER procedure [dbo].[prime_number]
(@@val int)
as
begin
declare @int int;
set @int=2;
while(@int < @@val)
          begin
if (@@val % @int=0)
            begin
            set @int=9;
break;
         end
          else
       set @int=@int + 1;
end
if (@int=9)
begin
print 'number is not prime number'
end
else
print  'NUMBER is prime number'
end

begin transaction | rollback transaction | how to retrieve deleted data | sql server 2008

how to rollback deleted query in sql server .
how to retrieve deleted | updated data in sql server by using rollback and begin transaction .
sometimes we think if i will delete that particular row then where it will effect.
specially when you are working on live project then it will give more impact.
so ,in this article i have shown how to retrieve deleted data with help of transaction and rollback in sql server 2005 | 2008.

SELECT * FROM STUD --it will give table data
id name
1 dev
2 deva
4 deva
5 deva
6 deva
8 "dsouza"
8 "d-souza"
 now i want to delete record where id=4 and after deletion i want to retrieve it .
then , what to do.
just write the query .

begin transaction 

delete from stud where id =4

(1 row(s) affected)

now check whether data deleted or it exists.

SELECT * FROM STUD
1 dev
2 deva
5 deva
6 deva
8 "dsouza"
8 "d-souza"


record is deleted .now i want to back fourth record what i will do , i will just write rollback .
rollback transaction
Command(s) completed successfully.


SELECT * FROM STUD
1 dev
2 deva
4 deva
5 deva
6 deva
8 "dsouza"
8 "d-souza"
here is your  record.but don't forget to use begin transaction  at the time of deletion of record.
else , it Will be difficult  to retrieve data again .

top query in sql server | select top 1*

How to use and write top query in sql server | My sql | Oracle
top query will be same in every database but  thing is that to know about table name and field of particular table.


select * from stud 

1 dev
2 deva
4 deva
5 deva
6 deva
8 "dsouza"
8 "d-souza"


Now I want to select one row using top .
select top 1* from stud

1 dev


Now I want to select last record using top.
select top 1* from stud order by name
8 "d-souza"

now its time to work something real means if i want to select 5th record in table where
there is  more than 100 or 8 record.
so , we have to use subquery for that and  create one  Instance table


SELECT TOP 1  FROM
(SELECT TOP 5 * FROM stud ORDER BY id ASC) AS LAST
ORDER BY id DESC

6 deva

what i did over here step first ..
i wrote top 5 * query so it will select top 5 records

SELECT TOP 5 * FROM stud ORDER BY id ASC
1 dev
2 deva
4 deva
5 deva
6 deva

i will get output like this.
now after that i want 5th record so i did top 1 * from table by creating  instance of table AS LAST.
using top you can select  5th record from  bottom or top.but you should know about syntax else it will give error .

Inner join | sql server | 2005-2008 | join query

SELECT        feesentry_tb.Paidfee, feesentry_tb.remain_fee, student.stud_fees
FROM            feesentry_tb INNER JOIN
                         student ON feesentry_tb.stud_rollno = student.stud_roll
                       
                         the above query showa inner join in sql server 2005 -2008.


SELECT        Paidfee, remain_fee,stud_fees
FROM            feesentry_tb INNER JOIN
                         student ON feesentry_tb.stud_rollno = student.stud_roll

second way to write inner join .

insert | update | delete query in sqlserver 2005-2008 with examples

select * from feesentry_tb.

1	300	300
2	200.34	344.98
3	203.34	344.98
3	203.34333	344.93338
4	203.34333	344.93338
NULL	NULL	NULL

DELETE FROM feesentry_tb
WHERE        (stud_rollno = 3)
after deleting two rows from table.
select * from feesentry_tb

1	300	300
2	200.34	344.98
4	203.34333	344.93338

UPDATE feesentry_tb SET Paidfee = 600 WHERE (remain_fee = 300)

1	600	300
2	200.34	344.98
4	203.34333	344.93338

How to insert valus into student table | sql server 2005-2008

How to insert valus into student table | sql server 2005-2008
insertion query .
INSERT INTO feesentry_tb
                         (stud_rollno, Paidfee, remain_fee)
VALUES        (1, 200, 300)
feesentry_tb is table name.
stud_rollno, Paidfee, remain_fee are fields of table.

how to create table in sql server 2005-2008

create table feesentry_tb
(
stud_rollno int,
Paidfee float(20),
remain_fee float(20),
)

in this example i have shown  how to create student fee table .
taken int and float don't assign value to int. else it will show error.

Top query | Order by | ascending | descending order in sql server 2005-2008

How to write Top query | Order by | ascending | descending order in sql server 2005-2008.

SELECT        TOP (1) cl_id, cl_name, cl_lst_name, cl_phone, cl_sal
FROM            clnt_tb
ORDER BY cl_lst_name DESC

or

SELECT        TOP (1) * from clnt_tb order by cl_lst_name desc

1	ram	singh	898979999	10000
NULL	NULL	NULL	NULL	NULL

SELECT        TOP (2) * from clnt_tb order by cl_lst_name desc
or

ELECT        TOP (2) cl_id, cl_name, cl_lst_name, cl_phone, cl_sal
FROM            clnt_tb
ORDER BY cl_lst_name DESC

1	ram	singh	898979999	10000
3	ramayan	rahi	443434343	12345

Order BY | Ascending | descending query in sql server 2005-2008

SELECT        cl_id, cl_name, cl_lst_name, cl_phone, cl_sal
FROM            clnt_tb
ORDER BY cl_lst_name

or

select * from  clnt_tb order by cl_lst_name

2	rama	hihi	8869	8889
3	ramayan	rahi	443434343	12345
1	ram	singh	898979999	10000

SELECT        cl_id, cl_name, cl_lst_name, cl_phone, cl_sal
FROM            clnt_tb
ORDER BY cl_lst_name DESC

or

select * from  clnt_tb order by cl_lst_name  desc

1	ram	singh	898979999	10000
3	ramayan	rahi	443434343	12345
2	rama	hihi	8869	8889

so , we got the idea how to use order by in  sql server with ascending order and descending order .
by default query execute ascending order.

Top Query in sql server 2005 | with example

How to use top query in sql server 2005.
select only one record from client table using top query.
Query to display single record using top query.



select * from clnt_tb

1    ram    singh    898979999    10000
2    rama    hihi    8869    8889
3    ramayan    rahi    443434343    12345

SELECT TOP (1) cl_id, cl_name, cl_lst_name, cl_phone, cl_sal
FROM clnt_tb

second way
SELECT TOP (1) *
FROM clnt_tb

1 ram singh 898979999 10000

5 types of error in visual basic Learn And Solve

Most of times this kind of error comes at compile time  in  .Net so try to solve this kind of error and clear logic and make your logic perfect.

1.         'variabler' is not a member of 'System.Data.DataTable'.
2.         'variable' is not a member of 'System.Data.DataTable'.  
3.         'close' is not a member of 'System.Data.DataTable'.  
4.         'variable' is ambiguous.  
5.         Type 'DataAccess' is not defined.

Types of Error In Sql Server | An object or column name is missing or empty. How to solve

3.1.An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
This error comes during Execution of Sql-server queries.
it shows that there is error in query like  double ""  ''fieldname ''  sometimes it need like  'fieldname'
only single ' or somewhere opening and closing parenthesis.
3.2.Incorrect syntax near ','
Msg 102, Level 15, State 1, Line 18
Just click on error line and check syntax

3.3The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Types of Error In C#.net | unmatching closed parenthesis

Error    5    Invalid expression term 'protected'  
Error    2    } expected
Error    7    Only assignment, call, increment, decrement, and new  object expressions can be used as a statement  

this kind of error comes because of unmatching closed parenthesis.
{}.

Logon failed. Details: crdb_adoplus : Object reference not set to an instance of an object. Error in File C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\temp_1abdb42c-f439-405a-a07f-627f8efa7dff {0E9A349B-44C0-44EC-BD65-2DF0B8EF4F79}.rpt: Unable to connect: incorrect log on parameters
This kind of  error comes during Displaying tha data using crystal report

How to create .Bak file of database in sql server using Query

Using SQL Query  wou can create >bak File of given database.
Just try  with given code.
BACKUP DATABASE [Db_Name] TO  DISK = N'E:\LogicalDb_Name.bak'
WITH NOFORMAT,
 NAME = N' Full Database Backup', 
GO
If After one or  Few Minutes you will get message like..
Processed 193200 pages for database 'YourDatabaseName', file 'YourDatabaseName_Data' on file 1.
Processed 1 pages for database 'YourDatabaseName', file 'YourDatabaseName_Log' on file 1.
BACKUP DATABASE successfully processed 193200 pages in 58.383 seconds (21.496 MB/sec).
Then Query Executed .
Just check in your folder whether there is .Bak file is present Or not.

Sql query For Creating .bak file in sql server 2005/2008 | with Full Database Backup

 Create .bak file of database within minute with extra features
BACKUP DATABASE [DB_Name] TO  DISK = N'E:\DBLogicalName.bak' WITH NOFORMAT, NOINIT,  NAME = N'Single-Full Database


Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

How to use ,convert(datetime) in sql server 2005 in query

This function we can use when we need to convert or change the Date time format.
InSql query how can we use it is shown as below.
convert(varchar(10),convert(datetime,admission_date),103) as  ,admission_date
                                                                field name          format