List in order

Say I have a table with the following files in them but not sorted and I
wanted to sort them in order like these based on the timestamp part of it.
How can I do so ?
DB1_tlog_200503122235.TRN
DB1_tlog_200503122240.TRN
DB1_tlog_200503122245.TRN
DB1_tlog_200503122250.TRN
DB1_tlog_200503122255.TRN
DB1_tlog_200503122300.TRN
DB1_tlog_200503122305.TRN
DB1_tlog_200503122310.TRN
DB1_tlog_200503122315.TRN
I think we need to find the datetime portion and that would be before the
".trn" and after the "_tlog_"
And then be able to sort the string "200503122235" which represents
2005-03-12 22:35 .. How can I do this ?
ThanksHassan
drop table #tEST
CREATE TABLE #Test
(
col VARCHAR(50) NOT NULL
)
INSERT INTO #Test VALUES ('200503122235')
INSERT INTO #Test VALUES ('200503122138')
INSERT INTO #Test VALUES ('200503121845')
INSERT INTO #Test VALUES ('200503122125')
INSERT INTO #Test VALUES ('200503122030')
INSERT INTO #Test VALUES ('200503122430')
SELECT *
FROM #Test
ORDER BY CONVERT(DATETIME,LEFT(col,4)+SUBSTRING(c
ol,5,2)+
SUBSTRING(col,8,2)+'
'+REPLACE(SUBSTRING(col,9,2),'24','00')+
':'+SUBSTRING(col,11,2) ,112)
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u1$UWDGKFHA.2796@.tk2msftngp13.phx.gbl...
> Say I have a table with the following files in them but not sorted and I
> wanted to sort them in order like these based on the timestamp part of it.
> How can I do so ?
> DB1_tlog_200503122235.TRN
> DB1_tlog_200503122240.TRN
> DB1_tlog_200503122245.TRN
> DB1_tlog_200503122250.TRN
> DB1_tlog_200503122255.TRN
> DB1_tlog_200503122300.TRN
> DB1_tlog_200503122305.TRN
> DB1_tlog_200503122310.TRN
> DB1_tlog_200503122315.TRN
> I think we need to find the datetime portion and that would be before the
> ".trn" and after the "_tlog_"
> And then be able to sort the string "200503122235" which represents
> 2005-03-12 22:35 .. How can I do this ?
> Thanks
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u1$UWDGKFHA.2796@.tk2msftngp13.phx.gbl...
> Say I have a table with the following files in them but not sorted and I
> wanted to sort them in order like these based on the timestamp part of it.
> How can I do so ?
> DB1_tlog_200503122235.TRN
> DB1_tlog_200503122240.TRN
> DB1_tlog_200503122245.TRN
> DB1_tlog_200503122250.TRN
> DB1_tlog_200503122255.TRN
> DB1_tlog_200503122300.TRN
> DB1_tlog_200503122305.TRN
> DB1_tlog_200503122310.TRN
> DB1_tlog_200503122315.TRN
> I think we need to find the datetime portion and that would be before the
> ".trn" and after the "_tlog_"
> And then be able to sort the string "200503122235" which represents
> 2005-03-12 22:35 .. How can I do this ?
If all of the filenames have the same prefix and extension, and all use the
above format for the date/time part, then it's simply a case of ordering the
results by that column. The date/time formatting is already fine for
sorting, so there's no need to convert it to a real date/time.
Dan