T-SQL函数整理

DECLARE @TheQuestion varchar(50)
DECLARE @TheAnswer int
DECLARE @TheDay datetime
DECLARE @RightValue int
DECLARE @LeftValue int
DECLARE @start int
DECLARE @length int
DECLARE @Answer varchar(50)
DECLARE @Question varchar(50)
DECLARE @Ultimate varchar(50)

SET @TheQuestion = 'What is The Answer To The Ultimate Question?'
SET @TheAnswer =  42
SET @TheDay = '5/1/2005'
SET @RightValue = 22
SET @LeftValue = 18
set @start=1
set @length=3
SET @Answer = 'Answer'
SET @Question = 'Question'
SET @Ultimate = 'Ultimate '

SELECT REPLACE(@TheQuestion, @Answer, @Question) as ReplaceAnswer ,
REPLACE(REPLACE(@TheQuestion, @Answer, @Question), @Ultimate+@Question, @Ultimate+@Answer) AS [The Switched Question]
SELECT RIGHT(@TheQuestion, @RightValue) AS [The Right Question]
SELECT LEFT(@TheQuestion, @LeftValue) AS [The Left Question]
SELECT SUBSTRING(@TheQuestion, @start, @length) AS [The Inner Question]
SELECT DATALENGTH(@TheQuestion) AS [The Wrong Answer]
SELECT @TheQuestion AS [The Question], @TheAnswer AS [The Answer], @TheDay AS [The Day]
SELECT LOWER(@TheQuestion) AS [The Lower Question]
SELECT UPPER(@TheQuestion) AS [The Upper Question]
SELECT REVERSE(@TheQuestion) AS [The Reverse Question]

SELECT GETUTCDATE() AS [System UTC Date and Time],GETDATE()AS [System Locale Date and Time],
CURRENT_TIMESTAMP AS [System Local Date and Time]

DECLARE @ValidDateString nvarchar(50)
DECLARE @InvalidDateString nvarchar(50)
SET @ValidDateString = '1/1/2014'
SET @InvalidDateString = '1/45/2014'
SELECT @ValidDateString AS [Valid Date String], ISDATE(@ValidDateString) AS [Is Valid?]
SELECT @InvalidDateString AS [Invalid Date String], ISDATE(@InvalidDateString) AS [Is Valid?]

DECLARE @Date DATETIME
SET @Date=GETDATE()
SELECT @Date,DATEPART(YEAR,@Date) AS [Current Year],DATEPART(MONTH,@Date) AS [Current Month],DATEPART(DAY,@Date) AS [Current Day Of Month],
DATEPART(HOUR,@Date) AS [Current Hour],DATEPART(MINUTE,@Date) AS [Current Minute],DATEPART(SECOND,@Date) AS [Current Second],
DATEPART(MILLISECOND,@Date) AS [Current Millisecond]

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate='2014-02-20 10:45:19.123'
SET @EndDate='2014-02-20 11:58:13.433'
SELECT DATEDIFF(YEAR, @StartDate ,@EndDate) AS [Years Between Dates]
,DATEDIFF(MONTH, @StartDate, @EndDate) AS [Months Between Dates]
,DATEDIFF(DAY, @StartDate, @EndDate) AS [Days Between Dates]
,DATEDIFF(HOUR, @StartDate, @EndDate) AS [Hours Between Dates]
,DATEDIFF(MINUTE, @StartDate, @EndDate) AS [Minutes Between Dates]
,DATEDIFF(SECOND, @StartDate, @EndDate) AS [Seconds Between Dates]
,DATEDIFF(MILLISECOND, @StartDate, @EndDate) AS [Session Duration In Milliseconds]

DECLARE @StartDate DATETIME
SET @StartDate='2014-02-20 10:45:19.123'
SELECT DATEADD(d, 42, @StartDate) AS [42 Days from today the Date will be:]
,DATEADD(d, -42, @StartDate) AS [42 Days ago the Date was:]

Supported by SQL SERVER 2012
DECLARE @Year int
DECLARE @Month int
DECLARE @Day int
SET @Year = 1776
SET @Month = 7
SET @Day = 4
SELECT DATEFROMPARTS(@Year,@Month,@Day) AS [Independence Day]

DECLARE @Date DATETIME
SET @Date=GETDATE()
SELECT 'The Current Date is: '+CAST(@Date AS varchar(50)) as [Current Date CAST]
,'The Current Date is: '+CONVERT(varchar(50),@Date,101) AS [Current Date Convert]
,'What is five times seven minus four?' AS [The Question], 'Why '+CAST(42 AS varchar(50))+', he said, confidently.' AS [The Answer]
,CONVERT(int,   0xAFAA) AS [Hex Int]

DECLARE @foo varchar(50)
DECLARE @bar varchar(50)
SET @foo='foo'
SET @bar='bar'
SELECT CASE WHEN @foo='foo' THEN @foo ELSE @bar END AS foo
SET @foo='foz'
SELECT CASE WHEN @foo='foo' THEN @foo ELSE @bar END AS foo
SET @foo='bar'
SET @bar='foo'
SELECT CASE WHEN @foo='bar' THEN 'foobar' WHEN @bar='foo' THEN 'barfoo' ELSE 'foo bar' END AS foobar
SELECT IIF(@foo='bar',@foo,@bar) AS foobar
SET @foo = null
SELECT ISNULL(@foo, @bar) AS foobar
SELECT COALESCE(@foo, @bar) AS foobar --COALESCE:Return the first non-null parameter

with cte_timecard
AS
(
SELECT 'EMP123' AS EmployeeId,  1 AS PeriodId, 8 AS DAY01, 8 AS DAY02, 8 AS DAY03, 8 AS DAY04, 8 AS DAY05, 4 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP456' AS EmployeeId, 1 AS PeriodId, 0 AS DAY01, 8 AS DAY02, 0 AS DAY03, 8 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP789' AS EmployeeId, 1 AS PeriodId, 8 AS DAY01, 8 AS DAY02, 8 AS DAY03, 4 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP123' AS EmployeeId,  2 AS PeriodId, 8 AS DAY01, 8 AS DAY02, 8 AS DAY03, 8 AS DAY04, 8 AS DAY05, 4 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP456' AS EmployeeId, 2 AS PeriodId, 0 AS DAY01, 8 AS DAY02, 0 AS DAY03, 8 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP789' AS EmployeeId, 2 AS PeriodId, 8 AS DAY01, 0 AS DAY02, 8 AS DAY03, 4 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP123' AS EmployeeId,  3 AS PeriodId, 8 AS DAY01, 8 AS DAY02, 8 AS DAY03, 8 AS DAY04, 0 AS DAY05, 4 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP456' AS EmployeeId, 3 AS PeriodId, 0 AS DAY01, 8 AS DAY02, 8 AS DAY03, 8 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
UNION
SELECT 'EMP789' AS EmployeeId, 3 AS PeriodId, 8 AS DAY01, 0 AS DAY02, 8 AS DAY03, 4 AS DAY04, 8 AS DAY05, 0 AS DAY06, 0 AS DAY07
)
SELECT EmployeeId, PeriodId, Day, Hours
FROM cte_timecard
UNPIVOT
(Hours FOR DAY IN (DAY01, DAY02, DAY03, DAY04, DAY05, DAY06, DAY07)) unpvt