SQL Server : Query XML

create function dbo.myAppDateTest(@ReqID nvarchar(30))
RETURNS varchar(30)
AS
BEGIN
DECLARE @FileContents XML
BEGIN
SELECT @FileContents = flags from tblapptrequest WHERE REQUESTID=@ReqID
END
return(
SELECT
x.item.value('appdate[1]','CHAR(10)')
FROM
@FileContents.nodes('SampleXML') AS x(item))
end

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

select cast(flags as xml).query('appdate').value('appdate[1]','char(10)') as appdate from tblapptrequest where requestid='439'

SQL Server Time Formats, SQL Server Time Format Examples

SELECT cast(datediff(mi,'11:00 AM','7:10 PM')/60 as varchar(10))+':'+cast(datediff(mi,'11:00 AM','7:10 PM')%60 as varchar(10))+':00'

SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinute

SELECT convert(varchar(10),getdate(),114)

SELECT right(CONVERT( varchar, getDate(), 100),7)

SELECT RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),7)

SELECT REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),7)),7),'AM',' AM'),'PM',' PM')

SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)

SELECT stuff( right( convert( varchar(26), getDate(), 109 ), 15 ), 7, 7, ' ' )

SELECT RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar,getDate(),100),8)),7)

SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5)

+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

SELECT

GETDATE() AS CurrentDate,

RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,

CONVERT(VARCHAR(10), GETDATE(), 101) + ' ' + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

SQLServer Find nth lowest salary from Employee

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 8 salary
FROM tblemployee
ORDER BY salary) a
ORDER BY salary DESC


You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

Query to Find the Second/Nth Highest Column Value in a Table

SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS E ORDER BY Salary ASC

Note that if we had to get the fourth highest Salary, we could do so by simply changing the subquery from TOP 2 to TOP 4

Query to Find the Second Lowest Column Value in a Table
SELECT TOP 1 Salary FROM (SELECT TOP 2 Salary FROM Employee ORDER BY Salary ASC) AS E ORDER BY Salary DESC

SQL SERVER – Find Nth Highest Salary of Employee

SQL SERVER – Find Nth Highest Salary of Employee

The following solution is for getting 6th highest salary from Employee table ,

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

You can change and use it for getting nth highest salary from Employee table as follows

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

SQLServer useful SQL Server DateTime functions.

SQLServer useful SQL Server DateTime functions.

—-Today
SELECT GETDATE() ‘Today’
—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) ‘Yesterday’
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) ‘First Day of Current Week’
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) ‘Last Day of Current Week’
—-First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) ‘First Day of Last Week’
—-Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) ‘Last Day of Last Week’
—-First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) ‘First Day of Current Month’
—-Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) ‘Last Day of Current Month’
—-First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) ‘First Day of Last Month’
—-Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) ‘Last Day of Last Month’
—-First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) ‘First Day of Current Year’
—-Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) ‘Last Day of Current Year’
—-First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) ‘First Day of Last Year’
—-Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) ‘Last Day of Last Year’

SQL SERVER – Get Time in Hour:Minute Format from a Datetime

SQL Server 2000/2005

SELECT
CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly
GO

SQL Server 2008

SELECT
CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
CONVERT(DATE,GETDATE(),101) AS DateOnly
GO

SQL SERVER – 2008 – Get Current System Date Time

SELECT GETDATE() AS CurrentDateTime

SELECT 'SYSDATETIME' AS FunctionName, SYSDATETIME() AS DateTimeFormat
UNION ALL
SELECT 'SYSDATETIMEOFFSET', SYSDATETIMEOFFSET()
UNION ALL
SELECT 'SYSUTCDATETIME', SYSUTCDATETIME()
UNION ALL
SELECT 'CURRENT_TIMESTAMP', CURRENT_TIMESTAMP
UNION ALL
SELECT 'GETDATE', GETDATE()
UNION ALL
SELECT 'GETUTCDATE', GETUTCDATE()

SQL Examples

http://silvernight.wordpress.com/