Order By DateTime in Varchar column in SQL Server

sofia
 
on Jun 01, 2021 12:16 AM
469 Views

hi sir,

how to order my record according to date and time also.

i mean my database is saving record contineously and randomly.

i means first it save record AM than PM and than again AM with respect to same date.

how can i modify my query in order with date and time as well

pingtime                        average

May 30 2021 1:06AM        100

May 30 2021  1:09PM        100

May 30 2021  1:20AM        75

May 29 2021  2:06AM        75

May 29 2021  2:35PM        100 

May 29 2021  3:40AM        50

May 30 2021  1:23PM        100

May 30 2021  1:35AM        50

May 31 2021  1:11PM       100

May 31 2021  1:23AM       50

May 31 2021  1:25PM       100 

i run the below query

SELECT [pingtime],[average] FROM [dbo].[graph] 
WHERE ipaddress='192.168.15.167' AND CONVERT(DATE,pingtime,103) BETWEEN CONVERT(DATE,DATEADD(DAY, -7, GETDATE()),103) AND CONVERT(DATE,GETDATE(),103) 
order by pingtime;
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 01, 2021 03:59 AM

Hi sofia,

Use below query.

SQL

SELECT [pingtime],[average] 
FROM [dbo].[graph] 
WHERE ipaddress='192.168.15.167' 
AND CONVERT(DATE,pingtime,103) BETWEEN CONVERT(DATE,DATEADD(DAY, -7, GETDATE()),103) AND CONVERT(DATE,GETDATE(),103) 
ORDER BY CONVERT(DATETIME,[pingtime],103) ASC

Output

pingtime average
May 29 2021 2:06AM 75
May 29 2021 3:40AM 50
May 29 2021 2:35PM 100
May 30 2021 1:06AM 100
May 30 2021 1:20AM 75
May 30 2021 1:35AM 50
May 30 2021 1:09PM 100
May 30 2021 1:23PM 100
May 31 2021 1:23AM 50
May 31 2021 1:11PM 100
May 31 2021 1:25PM 100