DATEDIFF Function in SQL Server

In this post i would like to show you to find the difference between two dates using DATEDIFF in SQL Server.

Syntax: DateDiff(DatePart, StartDate, EndDate)

DatePart is the parameter on which SQL Server calculates the difference between two input dates i.e. startdate and enddate.

Please refer to the below table:
Datepart Abbreviations

DatePart	Abbreviations
Year	yy, yyyy
quarter	qq, q
Month	mm, m
dayofyear	dy, y
Day	dd, d
Week	wk, ww
Hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Ex: To calculate the difference between two dates (No Of Days)
SELECT DATEDIFF(DAY,'2001-01-01','2010-01-01') AS No_Of_Days_Difference
Output:
No_Of_Days_Difference

---------------------
3287

Ex: To calculate the difference between two dates (No Of  Years)
SELECT DATEDIFF(YEAR,'2001-01-01','2010-01-01') AS No_Of_Years_Difference
Output:
No_Of_Years_Difference

---------------------
9


Ex: To calculate the difference between two dates (No Of Months)
SELECT DATEDIFF(MONTH,'2001-01-01','2010-01-01') AS No_Of_Months_Difference
Output:
No_Of_Months_Difference
---------------------
108

Reference : Vijaya Kadiyala(www.DotNetVJ.com)

DotNetVJ News - 500th Article

This is my 500th article and I very happy and excited about it. I started this blogging on 18-NOV-2007 to share my knowledge and build my own repository of what I am working on. I started this blog on .Net and then slowly started writing articles on SQL Server, PowerShell and MS Office Tips and Tricks. During this course of blogging i was awarded Microsoft Most Valuable Professional in ASP.NET (2008-2009) and in SQL Server (2009-2010). I am very happy and will continue to share my knowledge.

Find the Database creation date (DATABASE AGE) in SQL Server

In this post i would like to show you simple tip to find when the database created.

SELECT name,crdate 
FROM master..sysdatabases

Database->Date Of Creation

-------------------------------
master-> Apr 8 2003 9:13AM
tempdb-> Jan 29 2010 9:22PM
model-> Apr 8 2003 9:13AM
msdb-> Jul 9 2008 4:46PM
AdventureWorks-> Aug 31 2009 9:49AM
AdventureWorksDW-> Aug 31 2009 9:50AM
AdventureWorksLT-> Aug 31 2009 9:50AM
AdventureWorks2008-> Aug 31 2009 9:50AM
AdventureWorksDW2008-> Aug 31 2009 9:51AM
AdventureWorksLT2008-> Aug 31 2009 9:51AM

(13 row(s) affected)

Please let me know if you have any other way to find the same information...

Table Scan Vs Index Scan in SQL Server

Today i would like to clarify the difference between the Index Scan and Table Scan.

A table scan, table is processed row by row from start to end. In the index scan, index is processed row by row from start to end.

If the index is a clustered index then an index scan is really a table scan.

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

Convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server

Today, I would like to give you simple tip to convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server.
declare @String varchar(MAX)

set @String = '100,200,250,300,350,450,500'
set @String =','+ @String + ','
SELECT REPLACE(Val_Column,',','')
FROM
(
select
substring(@String,number,CHARINDEX(',',@String,number+1)-number) As Val_Column
from
master..spt_values
where number < LEN(@String)
and type = 'P'
) C
where Val_Column like ',%'

Output

--------------------------------------
100
200
250
300
350
450
500

(7 row(s) affected)

Split Full Name into First Name, Middle Name, Last Name in SQL Server using PARSENAME function

In this post i would like to show you a simple technique to split the full name. There are various techniques available to do the same using LEFT, RIGHT,SUBSTRING, CHARINDEX functions.

DECLARE @SQLVariable VARCHAR(100);



SET @SQLVariable = 'Vijaya.Krishna.Kadiyala.SQL Server';


select PARSENAME(@SQLVariable,1) AS Technical_Skill;


select PARSENAME(@SQLVariable,2) AS LAST_NAME;


select PARSENAME(@SQLVariable,3) AS MIDDLE_NAME;


select PARSENAME(@SQLVariable,4) AS FIRST_NAME;

Output:
Technical_Skill

-----------------
SQL Server
(1 row(s) affected)



LAST_NAME
-------------------
Kadiyala
(1 row(s) affected)


MIDDLE_NAME
-------------------
Krishna

(1 row(s) affected)



FIRST_NAME
-------------------
Vijaya

(1 row(s) affected)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)