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.