SQL FUNCTIONS THEORY

1. SQL Server functions are devided into two types.that are
2. 1. SYSTEM FUNCTIONS            2.USER DEFINED FUNCTION

1.SYSTEM FUNCTIONS:

                                    System functions allow you to access information from Microsoft® SQL Server™ system tables without accessing the system tables directly.

                                     The names of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of SQL Server  the @@functions are referred to as global variables, they are not variables and do not have the same behaviors as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.

SYSTEM DEVIDED INTO SO MANY TYPES:

                                1.Aggregate functions

                            2.Configuration functions

                            3.Cursor functions

                            4.Date and Time functions

                            5.mathematical  functions

                            6.metadata functions

                            7.Hierarchey Id functions

                            8.Rowset functions 

                            9.security functions

                           10.string functions

                            11.system and statistical functions

                            12.Text and Image functions

                             13.other functions

Date and Time functions:

http://www.codeproject.com/Articles/566542/Date-and-Time-Data-Types-and-Functions-SQL-Server






---------CURRENT SYSTEM DATE AND TIME

SELECT GETDATE()

-----CURRENT SYS ONLY YEAR

SELECT YEAR(GETDATE())

--IN EMP TABLE ANY EMPLOYEES ARE HIRING IN LEAPYEAR

SELECT * FROM EMP WHERE YEAR(HIREDATE)%4=0
SELECT YEAR(GETDATE())%4

-----CURRENT SYS ONLY MONTH


SELECT MONTH(GETDATE())

----CURRENT DAY

SELECT DAY(GETDATE())

---LAST MONTH LAST DATE

SELECT DATEADD(DAY,-DAY(GETDATE()),GETDATE())

---PRASENT MONTH FIRST DATE

SELECT DATEADD(DAY,-DAY(GETDATE())+1,GETDATE())

----WE GET THIS MONTH 10TH 

SELECT DATEADD(DAY,-DAY(GETDATE())+10,GETDATE())

---GET15TH OF THIS MONTH

SELECT DATEADD(DAY,-DAY(GETDATE())+15,GETDATE())

---THIS MONTH LAST DATE

DECLARE @DATE DATETIME 
SET @DATE = '2009/10/17'
SELECT DATEADD(DAY, 0 - DAY(DATEADD(MONTH, 1 , @DATE)), DATEADD(MONTH, 1 , @DATE))



SELECT DATEADD(DAY,-DAY(GETDATE()),DATEADD(MONTH,1,GETDATE()))


CREATE FUNCTION [DBO].[UDF_GETLASTDAYOFMONTH] 
(
    @DATE DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(D, -1, DATEADD(M, DATEDIFF(M, 0, @DATE) + 1, 0))

END

SELECT DATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0))
SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-----IT WILL GET NEXT MONTH FIRSTDATE
SELECT DATEDIFF(MONTH,0,GETDATE())

SELECT CAST(EOMONTH(GETDATE()) AS DATETIME)



-- FIRST DAY AND LAST DAY OF CURRENT MONTH

DECLARE @DT DATETIME,@D1 DATETIME,@D2 DATETIME
DECLARE @I INT
SELECT @DT=GETDATE()
SELECT @I=DAY(GETDATE())-1
SELECT @D1=DATEADD(DD,-@I,@DT)
SELECT @D2=DATEADD(MM,1,@D1)-1
SELECT @D1,@D2


----PREVIOUS MONTH FIRST DAY AND LAST DAY:

DECLARE @DT DATETIME,@D1 DATETIME,@D2 DATETIME
DECLARE @I INT
SELECT @DT=GETDATE()
SELECT @I=DAY(GETDATE())-1
SELECT @D1=DATEADD(MM,-1,DATEADD(DD,-@I,@DT) )
SELECT @D2=DATEADD(MM,1,@D1)-1
SELECT @D1,@D2

SELECT DATEADD(MONTH,-1,DATEADD(DAY,-DAY(GETDATE())+1,GETDATE()))

PREVIOUS MONTH FIRSTDATE:

SELECT DATEADD(MONTH,1,DATEADD(DAY,-DAY(GETDATE())+1,GETDATE()))-1


-----LAST MONTH LASTDATE DATE


SELECT DATEADD(DAY,-DAY(GETDATE()),GETDATE())



STRING FUNCTIONS:

       

                             

         

1. 
   
2. 
   
3. 
   
4. 
   
5. 
   

1. 
   
2. 
   
3. 
   

HOW TO ELIMINATE DUPLICATES ROWS FROM A TABLE

EXAMPLE1:

create table EE(col1 int, col2 int, col3 char(50))

insert into EE values (1, 1, 'data value one')

insert into EE values (1, 1, 'data value one')

insert into EE values (1, 2, 'data value two')

SELECT * FROM EE

--The first step is to identify which rows have duplicate primary key values:

SELECT col1, col2, count(*)

FROM EE

GROUP BY col1, col2

HAVING count(*) > 1

--This will return one row for each set of duplicate PK values in the table. The last column in this result is the number of duplicates for the particular PK value.

     

  --   If there are only a few sets of duplicate PK values, the best procedure is delete these manually on an individual basis. For example:

set rowcount 1

delete from EE

where col1=1 and col2=1

EXAMPLE2: