Q: time convert

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.Jim,
I would try an Instr() function to find the position of the ":" and then
split the hours from the minutes (6) (30) with a mid() function; then sort on
the two values. But I usually find the hard way -- I'm sure there is an
easier way.
:-)
HTH
Jim
"JIM.H." wrote:
> 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.
>|||If you want to do the conversion in the SQL query, you could try and
use the Convert(datetime, field) function, not sure if it will accept
just the time. If not prefix string, eg
Convert(datetime, '1901/01/01 ' + field)
Then you can do comparisions and extract specific parts (hours, minutes
etc).
Chris
Jim_OLAP wrote:
> Jim,
> I would try an Instr() function to find the position of the ":" and
> then split the hours from the minutes (6) (30) with a mid() function;
> then sort on the two values. But I usually find the hard way -- I'm
> sure there is an easier way.
> :-)
> HTH
> Jim
>
> "JIM.H." wrote:
> > 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.
> >
> >

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.
> > >
> >
> >
> >