Interview Feeds

Interview Questions and Answers Guide

Friday, June 13, 2008

Date sorting issue in TSQL!

In MS SQL Server 2000 on back end and on front end i tried to to show the data while accessing data from table i was trying to sort date in descending order but it shows below invalid order.

4/10/2008
4/10/2008
4/10/2008
4/15/2008
4/15/2008
4/9/2008
4/9/2008
4/9/2008
9/10/2007


the exact order that i wants was

4/9/2008
4/9/2008
4/9/2008
4/10/2008
4/10/2008
4/10/2008
4/15/2008
4/15/2008
9/10/2007

which was not coming in front so me for this i tried the blow methods.
Even i used below code in c# to sort my data table before its display but still sorting is not valid.
C#
//datatable is populated before all the below lines
//we can assign datatable as DataScource to data grid and bind also
//but the result will be the same as i discussed in this pose
DataView dv = datatable.DefaultView;
dv.Sort = "TaskDate";
// "TaskDate" is column of datatable
this.dgTaskReport.DataSource = dv;
this.dgTaskReport.DataBind();
//dgTaskReport is data grid name

But actually i was populating the datatable using stored procedure and in my stored procedure i was getting the date as it so the stored procedure was taking date as string now i converted the date into exact date formate so the sorting is fine as i want.
stored procedure is as under
TSQL

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'spBookTask' AND type = 'P')
DROP PROCEDURE spBookTask
GO
CREATE PROCEDURE spBookTask ( @bookid varchar(50))
AS
SELECT dbo.ufn_GetDateOnly(tblTaskSheet.TaskDate) as TaskDate
FROM tblTaskDetails
WHERE (tblTaskDetails.BookId = @bookid)
Order by convert(varchar(16), tblTaskDetails.TaskDate, 101) asc
GO

spBookTask '10003'

Regards.
Muhammad Hussain
Global Guide Line Team.
Copyright © 2005-2006 www.globalguideline.com All rights reserved.

Subscribe via email

Enter your email address:

Delivered by FeedBurner