Extract Just Time from DATETIME data type in SQL Server 2000, SQL Server 2005 and SQL Server 2008

In this post i would like to list out the various methods to extract the time from DATETIME data type in SQL Server. Depends on the version of the SQL Server you need to use different methods. In SQL Server 2008 you can use any of these methods but in SQL Server 2005 and SQL Server 2000 only method 1,2 and 3 will work.

DECLARE @DT DATETIME

SET @DT = GETDATE()
-- Method 1:
SELECT CONVERT(VARCHAR(30),@DT,114)
Output:------------------------------



22:31:43:047
(1 row(s) affected)

-- Method 2:
SELECT CAST(@DT AS TIME)
Output:----------------



22:31:43.0470000
(1 row(s) affected)

-- Method 3:
SELECT DATEADD(DAY, DATEDIFF(DAY, @dt, 0), @dt)
Output:-----------------------



1900-01-01 22:31:43.047
(1 row(s) affected)

-- Method 4:
SELECT @DT - CAST(FLOOR(CAST(@DT AS FLOAT)) AS DATETIME)
Output:
-----------------------

1900-01-01 22:31:43.047
(1 row(s) affected)

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

sargable vs non-sargable queries

A condition in a query is said to be sargable if the SQL Server can take the advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). sargable stands for Search ARGument Able.

Non-sargable search arguments in the WHERE clause are:
IS NULL
"<>"
NOT
NOT EXISTS
NOT IN
NOT LIKE
LIKE '%something'


In a query if you have mix of Non-sargable and Sargable conditions then SQL Server might use covering index based on the columns used in SELECT Clause, WHERE Clause and JOIN clause. These covering indexes sometimes may not be appropriate to the query and can increase disk I/O. In adidtion to this, try to avoid using any functions on Indexed columns.

Non-sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LEFT(LastName,2) = 'Ma'

Sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LastName LIKE 'Ma%'


Both of these queries produce the same result but the first one is non-sargable and will run little slow compared to the second one which is sargable.

Reference : Vijaya Kadiyala (www.DotNetVJ.com)

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)