Useful functions for SQL Server

/* In built function */
---Date Function
--1.

select getDate() -- to display current date
select GETUTCDATE() -- current date in UTC time

select * from dual

--2. DATEPART ( datepart , date )
SELECT DATEPART(Year, GETDATE()) AS 'Year' --year,yy,y
SELECT DATEPART(Month, GETDATE()) AS 'Month Number' --month mm, m
SELECT DATEPART(dayofyear,GETDATE())AS 'Day of Year' --dy, y
SELECT DATEPART(day,GETDATE()) AS 'Day' --dd, d
SELECT DATEPART(week,GETDATE()) AS 'Week' --wk, ww
SELECT DATEPART(weekday ,cast('02/18/2007'as datetime)) AS 'WeekDay' --GETDATE()) -- dw
SELECT DATEPART(hour,GETDATE()) AS 'Hour' -- hh
SELECT DATEPART( minute,GETDATE()) AS 'Minute' -- mi, n
SELECT DATEPART(second,GETDATE()) AS 'Second' -- ss, s
SELECT DATEPART(millisecond,GETDATE()) AS 'Millisecond' -- ms

--3. DATENAME ( datepart , date )
SELECT DATENAME(Year, GETDATE()) AS 'Year' --year,yy,y
SELECT DATENAME(Month, GETDATE()) AS 'Month Number' --month mm, m
SELECT DATENAME(dayofyear,GETDATE())AS 'Day of Year' --dy, y
SELECT DATENAME(day,GETDATE()) AS 'Day' --dd, d
SELECT DATENAME(week,GETDATE()) AS 'Week' --wk, ww
SELECT DATENAME(weekday ,cast('02/18/2007'as datetime)) AS 'WeekDay' --GETDATE()) -- dw
SELECT DATENAME(hour,GETDATE()) AS 'Hour' -- hh
SELECT DATENAME( minute,GETDATE()) AS 'Minute' -- mi, n
SELECT DATENAME(second,GETDATE()) AS 'Second' -- ss, s
SELECT DATENAME(millisecond,GETDATE()) AS 'Millisecond' -- ms

--4.Day Function
select Day(GETDATE()) AS 'Day'
select Month(GETDATE()) AS 'Month'
select Year(GETDATE()) AS 'Year'
select DATEPART("year",GETUTCDATE())

--5. DATEDIFF(datepart, startdate, endate)
SELECT DATEDIFF(Month,cast('01/18/2007'as datetime) ,GETDATE()) AS 'ExpMonth'

--6.DATEADD(datepart, number, date)
SELECT DATEADD(Month,3 ,GETDATE()) AS 'Last Day of training'

/* String Function
The following scalar functions perform an operation on a string input value and return a string or numeric value:*/
select lower('CYBAGE')
select upper('cybage')
select len('Cybage')
select DATALENGTH('Cybage')
select ascii('A')
select char(65)
select charindex('jil','jack and jil')
select charindex('jil','jack and jil',7)
select DIFFERENCE ( 'smith' ,'smeeth' )
select LEFT('jack and jil',7)
select Right('jack and jil',3)
SELECT QUOTENAME('abc"def')
select PATINDEX ( '%pattern%' ,'Testing pattern index function' )
select str(126.89,5,2)
select space(5)+'test'
select REPLACE ('Jack','J','B')
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
select ltrim(' cybage')
select rtrim(' cybage')
select reverse('test')
select substring('jack and jil',2,3)

DECLARE @nstring nchar(12)
SET @nstring = N'Åkergatan 24'
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring))

select @@CONNECTIONS

select CURRENT_USER
SELECT DB_NAME()

--Conversion function
/*
Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])*/
select * from emp where hiredate = '12/17/1980'
select * from emp where hiredate = cast('12/17/1980' as datetime)

/*Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )*/
select convert(datetime,'12/12/2007',103)
select CONVERT(XML, '',1)

select 'Salry of '+ename +' is '+ cast(sal as varchar) from emp

/*aggrigate function*/
select * from emp order by sal desc
select * from dept


select deptno,min(Sal)Min_Sal ,max(sal) Max_Sal,avg(sal) Avg_sal,count(*) No_of_Employee from emp group by deptno
select deptno,min(Sal)Min_Sal ,max(sal) Max_Sal,avg(sal) Avg_sal,count(*) No_of_Employee from emp group by deptno Having deptno=20

/*Department wise ,designation wise total employee */
SELECT deptno, job,count(empno) 'No_of_emp'
FROM emp GROUP BY deptno,job WITH rollup

/*Department wise ,designation wise total employee along with designation wise total employee*/
SELECT deptno, job,count(empno) 'No_of_emp'
FROM emp GROUP BY deptno,job WITH cube

Recursion For Binding Treeview

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string substringDirectory;
public Form1()
{
InitializeComponent();
directoryTreeView.Nodes.Clear();

String path = "E:\\Mohit";

directoryTreeView.Nodes.Add(path);
PopulateTreeView(path, directoryTreeView.Nodes[0]);
}
public void PopulateTreeView(string directoryValue, TreeNode parentNode)
{
string[] directoryArray =
Directory.GetDirectories(directoryValue);

try
{
if (directoryArray.Length != 0)
{
foreach (string directory in directoryArray)
{
substringDirectory = directory.Substring(
directory.LastIndexOf('\\') + 1,
directory.Length - directory.LastIndexOf('\\') - 1);

TreeNode myNode = new TreeNode(substringDirectory);

parentNode.Nodes.Add(myNode);

PopulateTreeView(directory, myNode);
}
}
}
catch (UnauthorizedAccessException)
{
parentNode.Nodes.Add("Access denied");
} // end catch
}
}
}