Convert four Digit number into hour format SQL

Question

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?

What SQL implemenation are you using? SQL Server/T-SQL? Oracle? DB2? MySql? date/time manipulation is probably the one area of SQL that varies most from vendor to vendor. — Nicholas Carey, Aug 29 '12 at 21:25
It is not any of those. I am not sure what it is, I asked my boss and he said it is a totally different form. It is often similar to Oracle. But if you can give me a function name, I can look it up in our documentation, I just need somewhere to start! — eatonphil, Aug 29 '12 at 21:27
Sorry, I'm still new to in-depth SQL, if you mean functions and things like that, no I cannot. — eatonphil, Aug 29 '12 at 21:30
Could you show me how I would use CAST? Like CAST (Start_Time AS *WHATGOESHERE?*) — eatonphil, Aug 29 '12 at 21:37
Do you want the result to be a string representation like "8:00" or a real DateTime/Time type ? — Magnus, Aug 29 '12 at 21:45
If you just need to insert a ":" in the middle, just do: `select substring(col, 1, 2) + ':' + substring(col, 3, 5)` — Magnus, Aug 29 '12 at 21:59

score 2 · Accepted Answer · answered Aug 29 '12 at 22:00

2

For CacheSQL, you can do this:

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

answered Aug 29 '12 at 22:00

PinnyM

35,165
3
73
81

fancyPants · Answer 2 · 2012-08-29T21:50:45.507

0

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

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.

edited Aug 29 '12 at 21:50

answered Aug 29 '12 at 21:39

fancyPants

50,732
33
89
96

That's a start! It did recognize that function, however I do not need it in year--mm-dd. I need it in hh:mm – eatonphil Aug 29 '12 at 21:41
Don't get confused by the function name, it will get you hh:mm . At least I can say this for sure for MySQL, but I bet it's the same for Oracle. – fancyPants Aug 29 '12 at 21:43
Sorry, it's still converting it to a yyyy-mm-dd format or similar. – eatonphil Aug 29 '12 at 21:45

score 0 · Answer 3 · answered Aug 29 '12 at 21:40

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.

score 0 · Answer 4 · edited Mar 19 '20 at 15:10

0

left( case when (EndTime / 100) < 10 then  ('0'+ convert(varchar, EndTime / 100 )) else convert(varchar, EndTime / 100 )  end, 2 )
                 + ':'
                 + right( '0' + convert(varchar, EndTime % 100 ) , 2 )

edited Mar 19 '20 at 15:10

Amit Joshi

15,448
21
77
141

answered Mar 19 '20 at 13:57

Nitesh Porwal

1

4

You should explain your answer! – Ingus Mar 19 '20 at 14:08

Convert four Digit number into hour format SQL

4 Answers4

Linked