Monday, February 20, 2012

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

No comments:

Post a Comment