Sort DateTime format for string literal

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 107
We have a table defined as

declare @t table (WorkRequestId varchar(100))
insert into @t values
('07052018080504623'),('07062018012756663'),('07062018020148130'),('07062018095201231'),
('07062018102203805'),('07062018103718059'),('07062018110304836'),('07062018115356135'),
('07062018120624983'),('07062018124035480'),('07062018080504623'),('07062018070504623')

select
*
from @t


We need to sort them as per Datetime format . The below code will do so

declare @t table (WorkRequestId varchar(100))
insert into @t values
('07052018080504623'),('07062018012756663'),('07062018020148130'),('07062018095201231'),
('07062018102203805'),('07062018103718059'),('07062018110304836'),('07062018115356135'),
('07062018120624983'),('07062018124035480'),('07062018080504623'),('07062018070504623')

select
*
from @t
order by
cast(
SUBSTRING(WorkRequestId, 1, 2) -- Month
+ '/'
+ SUBSTRING(WorkRequestId, 3, 2) -- Day
+ '/'
+ SUBSTRING(WorkRequestId, 5, 4) --Year
+ ' '
+ SUBSTRING(WorkRequestId, 9, 2) -- Hour
+':'
+ SUBSTRING(WorkRequestId, 11, 2) -- Minute
+':'
+ SUBSTRING(WorkRequestId, 13, 2) -- Second
+'.'
+ SUBSTRING(WorkRequestId, 15, 3) -- Millisecond
as datetime)


output
--------
WorkRequestId
07052018080504623
07062018012756663
07062018020148130
07062018070504623
07062018080504623
07062018095201231
07062018102203805
07062018103718059
07062018110304836
07062018115356135
07062018120624983
07062018124035480

Comments or Responses

Login to post response