Wednesday, October 11, 2017

sql server - datetime to number

needed to transform minutes and seconds into a decimal number... it was not that easy as I thought originally... here the snippets:

I needed to find out the current hours, seconds and minutes

DATEPART(SECOND, CURRENT_TIMESTAMP)
DATEPART(MINUTE, CURRENT_TIMESTAMP)

DATEPART(HOUR,   CURRENT_TIMESTAMP)

(works for every part of the current timestamp...)

afterwards I needed to convert it to decimal and assemble the parts into one number using convert.

something like:
select convert(double(6, 2), @hour * 100 + @min) + convert(double(6, 2), @sec);

Now to cut off seconds we just need to convert the number to int again (as in the good old days of programming).

set @hourAndMin = convert(int, @hourAndMinAndSeconds)

No comments: