Datetime convertion

Question

Post reply

Datetime convertion

eroness

Newbie

Points: 6
More actions
October 29, 2010 at 6:04 am

#231052

Hi,
I am trying to convert DateTime type to Double in a query. I want convert 07:30.00 to return 7,5
Select Datepart(hh;timer)+(Datepart(mi;timer)/60) as Expr1
From tblTimeRecorder;
This sort of works, but the datatype that Sql server suggest returns no whole numbers.
Can anybody help me?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

eroness (10/29/2010)
This sort of works, but the datatype that Sql server suggest returns no whole numbers.

Suggest? No whole numbers? What do you mean?

Can you post the results you're getting and what instead you would expect?

-- Gianluca Sartori

eroness Newbie Points: 6 More actions · Answer 2

Sorry, it return only whole number (I think it's Integer).

The Datepart function for the minutes should return 0,5 but return 0.

SELECT skjema_nr, skjema_arb_lengde, DATEPART(mi, skjema_arb_lengde) / 60 AS Expr1
FROM trc.beregningsskjema

12000-01-01 07:30:00.0000

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Try this:

SELECT CAST(DATEPART(hh,timer) AS FLOAT) + (CAST(DATEPART(mi,timer) AS FLOAT)/60)

FROM tblTimeRecorder

BTW, get rid of that "tbl" prefix on your tables before Joe Celko catches you...

-- Gianluca Sartori

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

eroness (10/29/2010)
Sorry, it return only whole number (I think it's Integer).
The Datepart function for the minutes should return 0,5 but return 0.
SELECT skjema_nr, skjema_arb_lengde, DATEPART(mi, skjema_arb_lengde) / 60 AS Expr1
FROM trc.beregningsskjema
12000-01-01 07:30:00.0000

The RETURN TYPE of the DATEPART function is INTEGER. In your query you have divided the result of DATEPART(mi, skjema_arb_lengde) by 60( which is taken as an INTEGER by SQL Server). And any INTEGER divided by an INTEGER gives you an INTEGER.

In your case when 30 is divided by 60 the decimal places get truncated to give you 0 as the result. To solve this try the below query where we are deliberately adding decimal places to the number 60 which will make it NUMERIC or DECIMAL

SELECT skjema_nr, skjema_arb_lengde, DATEPART(mi, skjema_arb_lengde) / 60.00 AS Expr1

FROM trc.beregningsskjema

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

Gianluca Sartori (10/29/2010)
Try this:
SELECT CAST(DATEPART(hh,timer) AS FLOAT) + (CAST(DATEPART(mi,timer) AS FLOAT)/60)
FROM tblTimeRecorder
BTW, get rid of that "tbl" prefix on your tables before Joe Celko catches you...

:-D:-P:-D He has managed to scare even experienced people like you. He would be proud of the fact if he sees this.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

eroness Newbie Points: 6 More actions · Answer 6

Thank you both, Dhasian and Sartori!

Both suggestions worked fine!