Q: time conversion

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.You may find these articles helpful:
http://www.sommarskog.se/arrays-in-sql.html
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
--
Keith
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||Some examples:
select * from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by cast(timecol as datetime)
order by case when len(timecol) < 5 then '0' + timecol else timecol end
select cast(timecol as datetime) as newcol from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by newcol
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||or instead of
order by case when len(timecol) < 5 then '0' + timecol else timecol end
order by right('0' + timecol,5)
"Scott Morris" wrote:
> Some examples:
> select * from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by cast(timecol as datetime)
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> select cast(timecol as datetime) as newcol from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by newcol
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > Hello
> > I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> > these string to time with the same format (HH:MM) so that I can sort by
> time.
> > How should I do this?
> > Thanks,
> > Jim.
> >
>
>|||Good one. The left() function came to mind at first but I couldn't think of
a way to use it. I was looking at the wrong end!
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:29C1C2D5-CB91-4102-85BC-07A7F18146E5@.microsoft.com...
> or instead of
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> order by right('0' + timecol,5)
>
> "Scott Morris" wrote:
> > Some examples:
> >
> > select * from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by cast(timecol as datetime)
> > order by case when len(timecol) < 5 then '0' + timecol else timecol end
> >
> > select cast(timecol as datetime) as newcol from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by newcol
> >
> >
> > "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> > news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > > Hello
> > > I have string 6:10, 11:30, 23:10, ., in one of column. I need to
convert
> > > these string to time with the same format (HH:MM) so that I can sort
by
> > time.
> > > How should I do this?
> > > Thanks,
> > > Jim.
> > >
> >
> >
> >