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/

StartDate and Endate of the Month in C# and SQL Server

StartDate and Endate of the Month in C#

DateTime startDate= new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
DateTime endDate = startDate.AddMonths(1).AddDays(-1);



StartDate and Endate of the Month in SQL Server
select convert(nvarchar(8),dateadd(mm, datediff(mm, 0, '3/4/2009'), 0), 112)
select convert(nvarchar(8),dateadd( dd, -1, dateadd( mm, 1, dateadd( dd, -day('2/2/2009')+1, '2/2/2009'))),112)

Example in C#:: To get First day of the Month and Last day of the Month Using C#

DateTime givenDate=DateTime.Parse(TxtAppDate.Text);

int year=givenDate.Year;
int month=givenDate.Month;

DateTime startDate= new DateTime(year, month, 1);
DateTime endDate = startDate.AddMonths(1).AddDays(-1);

while(startDate <= endDate)
{
string appdate=startDate.ToShortDateString();
GetDetails(appdate);
startDate=startDate.AddDays(1);
}

SQL Querys examples Date-Time Difference

SQL Query examples Date-Time Difference

SQL example 1:
select checkindate, isnull(sum(lessthan2), 0) as lessthan2, isnull(sum(Gt2Lt5), 0) as Gt2Lt5, isnull(sum(Gt5Lt10), 0) as Gt5Lt10, isnull(sum(Gt10Lt15), 0) as Gt10Lt15, isnull(sum(Gt15), 0) as Gt15, sum(mins),sum(mins)/(isnull(sum(lessthan2), 0)+isnull(sum(Gt2Lt5), 0)+isnull(sum(Gt5Lt10), 0)+isnull(sum(Gt10Lt15), 0)+isnull(sum(Gt15), 0)) as Mins
from
(
select checkindate, mins,
'lessthan2'=
CASE
WHEN mins >= 0 and mins <>= 2 and mins <>= 5 and mins <>= 10 and mins <>= 15 THEN gt15+1
END

from
(
select lt2=0,lt5=0,lt10=0,lt15=0,gt15=0,checkindate,mins = cast(replace(datediff(mi,logintime,checkintime)%60,'-','') as Int) from tblactivity where (logintime<>'' and checkintime<>'' and checkindate between cast('01/01/2006' as datetime) and cast('07/01/2006' as datetime))
)a

)b group by checkindate




SQL example 2: Time difference calculation

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'




SQL example 3: Time difference calculation
select CT,ST,
Mins =cast(replace(datediff(mi,CT,ST)%60,'-','') as varchar(10)),'Hours'=

CASE
WHEN cast(replace(datediff(mi,CT,ST)/60,'-','')as int) < st="substring(flags,charindex('calledforservicetime_flag'," ct="checkintime">''

More SQL Server Examples @: http://silvernight.wordpress.com/