SQL QUERY Help

--Coinage

Declare @sal INT;

SET @sal =5988;

select @sal,@sal/1000 AS T1000,

         @sal%1000/500 AS T500,

         @sal%1000%500/100 AS T100,

         @sal%1000%500%100/50 AS T50,

         @sal%1000%500%100%50/20 AS T20,

         @sal%1000%500%100%50%20/10 AS T10,

         @sal%1000%500%100%50%20%10/5 AS T5,

         @sal%1000%500%100%50%20%10%5/2 AS T2,

         @sal%1000%500%100%50%20%10%5%2 AS T1

--Table Valued Function

CRAETE FUNCTION Coinage

(

@sal BIGINT

)

RETURNS TABLE

AS

RETURN

(SELECT @sal AS T,

             @sal/1000 AS T1000,

         @sal%1000/500 AS T500,

         @sal%1000%500/100 AS T100,

         @sal%1000%500%100/50 AS T50,

         @sal%1000%500%100%50/20 AS T20,

         @sal%1000%500%100%50%20/10 AS T10,

         @sal%1000%500%100%50%20%10/5 AS T5,

         @sal%1000%500%100%50%20%10%5/2 AS T2,

         @sal%1000%500%100%50%20%10%5%2 AS T1

         )

        

--SELECT * FROM Coinage(59216588345)

--OUTPUT Keyword

declare @i INT;

exec sp_executeSQL N' SELECT @i=5',N'@i INT OUTPUT',@i OUTPUT;

select @i+9

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

CREATE function [dbo].[Date](@Year int, @Month int, @Day int)

-- returns a datetime value for the specified year, month and day

returns datetime

as

    begin

  

    return dateadd(month,((@Year-1900)*12)+@Month-1,@Day-1)

    end

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

CREATE function [dbo].[DateOnly](@DateTime DateTime)

-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.

returns datetime

as

    begin

    return dateadd(dd,0, datediff(dd,0,@DateTime))

    end

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

CREATE function [dbo].[DateTime](@Year int, @Month int, @Day int, @Hour int, @Minute int, @Second int)

-- returns a dateTime value for the date and time specified.

returns datetime

as

    begin

    return dbo.Date(@Year,@Month,@Day) + dbo.Time(@Hour, @Minute,@Second)

    end

------------- XML DataType 

DECLARE @XMLTable TABLE(GameHistory XML)

INSERT INTO @XMLTable VALUES( '<game xmlns="http://my.name.space">

      <move>

       <player>white</player>

       <piece>pawn</piece>

       <start>A2</start>

       <end>A3</end>

      </move>

      <move>

       <player>black</player>

       <piece>pawn</piece>

       <start>D7</start>

       <end>D6</end>     

      </move>

     </game>');

WITH XMLNAMESPACES(DEFAULT 'http://my.name.space')

SELECT GameHistory.query('/game/move[1]') FROM @XMLTable

.....TO GET Defination/text/code of SP FUNCTION VIEW 

sp_helptext 'View_SMS'

sp_helptext 'SP_SMS'

sp_helptext 'FN_SMS'

------SQL QUERY for DateOnly and TimeOnly from DateTime

SELECT  GETDATE() AS theDateTime,

            DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AS theDate,

            DATEADD(dd, -DATEDIFF(dd, 0, GETDATE()), GETDATE()) AS theTime

Here 0 can be replaced with any number. 0 means CAST(0 AS DATETIME) that is 1900-01-01 00:00:00