使用时间排序 [英] Sorting using Time
本文介绍了使用时间排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我在下面的Varchar DataType日期:
23-NOV-18 08.27.00.640000000 AM
23-NOV-18 08.30.10.965000000 AM
18-JAN -18 09.58.16.288000000 AM
我需要根据时间对上述日期进行排序。请帮助。
解决方案
嗨Ramkaay,
请尝试以下脚本。
IF OBJECT_ID('test')IS NOT NULL drop table test
go
create table test
([date_time] varchar( 50))
插入测试值
('23 -NOV-18 08.27.00.640000000 AM'),
('23 -NOV-18 08.30.10.965000000 AM'),
('18 -JAN-18 09.58.16.288000000 AM')
; cte as(
SELECT *,
replace(右边的情况([date_time],2) ='AM'
然后substring([date_time],11,12)
else cast(cast(substring([date_time],11,2)as int)+12 as varchar(10))+ substring([date_time],13,10)end,'。',':')as [time] from test)
从cte
顺序中选择[date_time]作为cast([time]作为时间)
/ *
date_time
---------------------------------- ----------------
23-NOV-18 08.27.00.640000000 AM
23-NOV-18 08.30.10.965000000 AM
18-JAN- 18 09.58.16.288000000 AM
* /
希望它可以帮到你。
最好的问候,
拉结
Hi all,
I've the below Dates of Varchar DataType:
23-NOV-18 08.27.00.640000000 AM
23-NOV-18 08.30.10.965000000 AM
18-JAN-18 09.58.16.288000000 AM
I need to Sort Above dates based on time. Kindly assist.
解决方案
Hi Ramkaay,
Please try following script.
IF OBJECT_ID('test') IS NOT NULL drop table test go create table test ([date_time] varchar(50) ) insert into test values ('23-NOV-18 08.27.00.640000000 AM'), ('23-NOV-18 08.30.10.965000000 AM'), ('18-JAN-18 09.58.16.288000000 AM') ;with cte as ( SELECT *, replace(case when right([date_time],2)='AM' then substring([date_time],11,12) else cast(cast(substring([date_time],11,2)as int)+12 as varchar(10))+substring([date_time],13,10) end,'.',':') as [time] from test) select [date_time] from cte order by cast([time] as time) /* date_time -------------------------------------------------- 23-NOV-18 08.27.00.640000000 AM 23-NOV-18 08.30.10.965000000 AM 18-JAN-18 09.58.16.288000000 AM */
Hope it can help you.
Best Regards,
Rachel
这篇关于使用时间排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文