.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

Order By with DateTime column

Posted By: ASPEvil     Posted Date: November 11, 2010    Points:2   Category :Sql Server
Hello,

I have a column with the datatype "DateTime" and I want to use "Order By" with this column for date and time, means it will first order the data by date then by time. So, if there are data with the same date but different time, will be shown properly.

I have written the query, but it is not at all working for the time, means if there are data with same date but different time, then "order by" clause is not working to sort data by time.

Please let me know, how to accomplish this job.

Responses
Author: Sadhuryan             
Posted Date: November 11, 2010     Points: 5   

Here below is the query which will suits your scenario:

SELECT CONVERT(CHAR, GETDATE(), 104) AS DATE, CONVERT(CHAR, GETDATE(), 108) AS TIME ORDER BY DATE, TIME 



Replace 'GETDATE()' with your 'Column Name'.

Author: Sadhuryan             
Posted Date: November 11, 2010     Points: 5   

Here below is the query which will suits your scenario:

SELECT CONVERT(CHAR, GETDATE(), 104) AS DATE, CONVERT(CHAR, GETDATE(), 108) AS TIME ORDER BY DATE, TIME 



Replace 'GETDATE()' with your 'Column Name'.

Author: Suresh             
Posted Date: November 11, 2010     Points: 5   

Hello Friend,
If you are using above Sql-2008.Kindly go through below code. You wiil get exact result.

Declare @t table
(
datevalue nvarchar(25)
)
Insert into @t
Select '2010-11-11 10:28:34.163' Union all
Select '2010-11-11 07:28:34.163' Union all
Select '2010-11-10 15:28:34.163' Union all
Select '2010-11-09 05:28:34.163'

Select * from @t
Select Datevalue,Convert(date,datevalue) [Date],Convert(time,datevalue) [Time] from @t
Order By Convert(date,datevalue),Convert(time,datevalue)


--Original Result
datevalue
-------------------------
2010-11-11 10:28:34.163
2010-11-11 07:28:34.163
2010-11-10 15:28:34.163
2010-11-09 05:28:34.163


--Required Result
Datevalue Date Time
------------------------- ---------- ----------------
2010-11-09 05:28:34.163 2010-11-09 05:28:34.1630000
2010-11-10 15:28:34.163 2010-11-10 15:28:34.1630000
2010-11-11 07:28:34.163 2010-11-11 07:28:34.1630000
2010-11-11 10:28:34.163 2010-11-11 10:28:34.1630000


Let me know that, do you need any further clariffication.
Good Luck Dear



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend