如何排序上午和下午在sql选择查询的时间？ [英] how to sort a.m. and p.m. time in sql select query ?

问题描述

我有一个select查询，它根据需求返回时隙，但它以数字顺序排列，我希望它在A.M.中排序。到了P.M.格式。

HEre是我的查询..

 选择 不同时间来自 BAT.FacultyTiming_Master fm 
 其中 fm.Faculty_Name = '  Satendra Kumar Jangra' 和 fm.bookstatus = '  false' 

返回：

 1:00 PM  -  2:30 PM 
 10:00 AM  -  11： 30 PM 
 11:30 AM  -  1:00 PM 
 2:30 PM  -  4:00 PM 
 4:00 PM  -  5:30 PM 
 5:30 PM  - 7:00 PM 
 7:00 PM  -  8:30 PM 
 8:30 PM  -  10:00 PM 

但是我希望它像..

 10:00 AM  -  11:30 PM 
 11:30 AM  -  1:00 PM 
 PM  -  2:30 PM 
 2:30 PM  -  4:00 PM 
 4:00 PM  -  5:30 PM 
 5:30 PM  -  7:00 PM 
 7:00 PM  -  8:30 PM 
晚上8:30  - 晚上10:00 

解决方案

你可以这样做，但我宁愿建议你看看你数据库设计并将值存储为正确的类型而不是字符串值。

创建表#Timings（START NVARCHAR（100），ENDS NVARCHAR（ 100），Timing NVARCHAR（100））
 
 INSERT #Timings 
 SELECT 
 SUBSTRING（Timings，1，CHARINDEX（' - '，Timings） -  1），
 SUBSTRING（Timings，CHARINDEX（' - '，Timings）+ 1，LEN（Timings）），
时间
来自
 Faculty定时
订购
来自时间
 
 SELECT 
时间
 FROM 
 #Timings 
 ORDER BY 
 CONVERT（时间，开始）
 
 DROP TABLE＃定时

I have a select query which returns time slot as per the requirement but it comes in numeric order and i want it to be ordered in A.M. to P.M. format.
HEre is my query ..

select distinct timings from BAT.FacultyTiming_Master fm
where fm.Faculty_Name='Satendra Kumar Jangra' and fm.bookstatus='false'

which returns :

1:00 PM - 2:30 PM
10:00 AM - 11:30 PM
11:30 AM - 1:00 PM
2:30 PM - 4:00 PM
4:00 PM - 5:30 PM
5:30 PM - 7:00 PM
7:00 PM - 8:30 PM
8:30 PM - 10:00 PM

but i want it like ..

10:00 AM - 11:30 PM
11:30 AM - 1:00 PM
1:00 PM - 2:30 PM
2:30 PM - 4:00 PM
4:00 PM - 5:30 PM
5:30 PM - 7:00 PM
7:00 PM - 8:30 PM
8:30 PM - 10:00 PM

解决方案

You can do something like this, but I would rather suggest you to look at you database design and store the value as proper types instead of string values.

CREATE TABLE #Timings (START NVARCHAR(100), ENDS NVARCHAR(100), Timing NVARCHAR(100))

INSERT #Timings
SELECT 
	SUBSTRING(Timings, 1, CHARINDEX('-', Timings) - 1),
	SUBSTRING(Timings, CHARINDEX('-', Timings) + 1, LEN(Timings)),
	Timings
FROM 
	FacultyTiming 
ORDER 
	BY Timings

SELECT 
	Timing
FROM 
	#Timings 
ORDER BY 
        CONVERT(time , START)

DROP TABLE #Timings

这篇关于如何排序上午和下午在sql选择查询的时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！