Get the the difference between two dates in MS SQL

The DATEDIFF() function returns the difference between two dates
Syntax: DATEDIFF ( datepart , startdate , enddate )

Following is an example for the DateDiff()
DECLARE @startdate datetime ='2011-11-14 02:00:06.957';
DECLARE @enddate datetime = '2011-11-16 05:00:15.490';
SELECT DATEDIFF(day, @startdate, @enddate) as dayDifference,DATEDIFF(HOUR, @startdate, @enddate)as hourDifference
Output

dayDifference	hourDifference
2	51

Similarly the following are the available datepart Options on the DateDiff() Function.

year(or yy or yyyy)
quarter(or qq or q)
month (or mm or m )
dayofyear(or dy or y )
day (or dd, d )
week(or wk or ww)

hour(or hh)
minute(or mi or n)
second(or ss or s )
millisecond(or ms)
microsecond(or mcs)
nanosecond(or ns)
TZoffset(or tz)
ISO_WEEK(or isowk or isoww)

Get the Row Numbers as a separate column on MS SQL Query

I was trying to execute a new query where I need the Row number as a new column on the result itself. I used the SQL Function ROW_NUMBER() to get the row number.
Following query gives the Report Path with the HitCount along with the added Row Number.

select ROW_NUMBER() OVER(ORDER BY count(ReportPath) DESC) AS 'Row Number', ReportPath,count(ReportPath) as HitCount
FROM [ReportServer].[dbo].[ExecutionLog2]
Group by ReportPath
Order by HitCount desc


Cheers !

Get the Disk Usage Statistics from the WinDirStat !

WinDirStat is an opensource tool to identify howmuch of disk space you are using on your wondow box. It shows disk, file and directory sizes in a treelist as well as graphically in a treemap, much like KDirStat or SequoiaView.

Once i started using it, i feel like life would have been very hard without it.
Why don't you try it yourself.

You can download the tool from http://windirstat.info/

How to parse multi value parameter from SSRS Report !

While writing an SSRS Report, I had to parse a multivalued parameter and stored it on a temp table. After some googling i came up with the following solution. @id is the multi valued parameter that need to be parsed. Following is the sample code that you can used to parse and put the values on a temp table.


-- @uid is the parameter. Lets define the parameter for testing.

declare @uid as nvarchar(200)
set @uid='u01052901,u01052789,u01052897,u0105345,u08023432,u0234324,u23479879'

create table #TempIDTable (
slice varchar(50))
declare @index1 int
declare @u_id nvarchar(4000)
set @index1 = 1
if @uid is null
set @index1 = 0
while @index1 !=0
begin
set @index1 = charindex(',',@uid)
if @index1 !=0
set @u_id = left(@uid,@index1 - 1)
else
set @u_id = @uid
insert into #TempIDTable select @u_id
set @uid= right(@uid,len(@uid) - @index1)
if len(@uid) = 0
break
end

Select * from #TempIDTable
--drop table #TempIDTable

Date Conversion from YYYY-MM-DD HH:MM:SS:MMM to YYYY-MM-DD 00:00:00:000 !

Just keeping it handy !

declare @StartTime as DateTime
set @StartTime= dateadd(dd,0, datediff(dd,0,'2011-06-08 11:18:26.000'))
select @StartTime as StartTime