Convert four Digit number into hour format SQL

问题:

I have looked into Cast and Convert, but I cannot find a way to do this. I need to convert four digits into an hour format. For instance, 0800 would become 8:00 or 1530 would become 15:30. I cannot use functions, I'm using a InterSystem's CacheSQL. Any suggestions? Thanks in advance!

EDIT: If it is any more convenient, I can just divide the four digits by one hundred to get values like 15 from original 1500, or 8.30 from 0830. Does this make converting to hour:minute format easier?

回答1:

For CacheSQL, you can do this:

SELECT {fn TRIM(LEADING '0' FROM LEFT(col_name, 2) || ':' || RIGHT(col_name, 2)) }
FROM table_name

回答2:

In SQL Server 2008, given data that looks like

create table #data
(
  HHMM int not null ,
)
insert #data values ( 0800 )
insert #data values ( 0815 )
insert #data values ( 1037 )
insert #data values ( 2359 )

You can say:

select * ,
       strTime = right( '0' + convert(varchar, HHMM / 100 ) , 2 )
               + ':'
               + right( '0' + convert(varchar, HHMM % 100 ) , 2 ) ,
       myTime  = convert(time ,
                   right( '0' + convert(varchar, HHMM / 100 ) , 2 )
                 + ':'
                 + right( '0' + convert(varchar, HHMM % 100 ) , 2 ) ,
                 120
                 )
from #data

Other SQL implementations likely have similar functionality.

In earlier versions of SQL Server that lack the time datatype, just use datetime, thus:

select * ,
       strTime = right( '0' + convert(varchar, HHMM / 100 ) , 2 )
               + ':'
               + right( '0' + convert(varchar, HHMM % 100 ) , 2 ) ,
       myTime  = convert(datetime,
                   right( '0' + convert(varchar, HHMM / 100 ) , 2 )
                 + ':'
                 + right( '0' + convert(varchar, HHMM % 100 ) , 2 ) ,
                 120
                 )
from #data

You'll get a datetime value that is 1 Jan 1900 with the desired time-of-day.

回答3:

Well, if it is something like Oracle you might have a try with the to_date() function.

Read more here.

Example:

SELECT to_date(yourColumn, 'HH24MI') FROM ...

EDIT (why? see comments): If necessary (I'm actually not familiar with Oracle) you can wrap another function like TIME() around it.

SELECT TIME(to_date(yourColumn, 'HH24MI')) FROM ...

Read more about TIME() here.

</EDIT>

In MySQL the equivalent would be the STR_TO_DATE() function:

SELECT STR_TO_DATE(yourColumn, '%H%i') FROM ...

Read about STR_TO_DATE() and its parameters under the DATE_FORMAT() function.