Convert decimal(5) to time format

1

Hi All
I have time field that have been stored as decimal(5.0).
If the user wants to record the time as say 09:30, they simply type 0930.
Not sure why the field is decimal(5) instead of decimal(4) - but i guess that's another story?

Anyhow, I am wanting to convert this decimal value to a smalldatetime field in another DB.

After 2 days of trying countless formulae (eg: multiply by 1.666666667 and or 0.02777778 [being possible result generated by Excel formulae] and divide by 24/60/60
or using CONVERT, CAST and even combinations of CASE WHEN functions, I have still not found a solution.
How can this be done?
Thanks for any input.

2 
SELECT	DATEADD(hour, FLOOR(DecimalTime / 100) % 100,
		DATEADD(minute, (DecimalTime % 100),
			CONVERT(smalldatetime, '00:00')))

I prefer this as it is purely numeric, and therefore efficient, however it will accept 9999 as a value without error (and give you something around the 5th January ...)

You could convert to CHAR and then to SMALLDATETIME, but to my mind that is less efficient because of the datatype conversions. It would, however, Error on any time value > 2359

SELECT	CONVERT(smalldatetime,
		STUFF(
			RIGHT('0000' + CONVERT(varchar(5), DecimalTime), 4)
		, 3, 0, ':')
	)
3

You can shorten it slightly to use only one DATEADD:

SELECT CAST(DATEADD(MINUTE, FLOOR(decimal_time_column / 100) * 60 + decimal_time_column % 100, 0) AS smalldatetime)
FROM (
    SELECT CAST(0930 AS decimal(5, 0)) AS decimal_time_column
) AS test_data
1 Like
4

Well that's blinking obvious! You'd think I might have thought of that ... Old Age and all that ... :slight_smile:

5

Thanks to both of you.
I have got Kirstens version working by modifying and adding the 'From TableName Q' line as follows

SELECT strtim,DATEADD(hour, FLOOR(Q.StrTim / 100) % 100,
DATEADD(minute, (Q.StrTim % 100),
CONVERT(smalldatetime, '00:00')))
from TableName Q

However, I am curious to know what the percent sign is doing in this query as I thought that it is used as a wild card in SQL?

6

Its Modulo arithmetic - the "remainder" after the division.