Set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Set

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-07-06 : 10:59:49

Hi,

I have execute the below select statment in query analyzer. Its returns me 0 [Zero], but the actual difference between these two dates is of 15 minutes.

select datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60

it should return: 0.15

can anybody tell me, which is the SET command to get such required o/p.

thanks in avance,

Mahesh

pootle_flump

1064 Posts

Posted - 2007-07-06 : 11:02:09

[code]select 1e0 * datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:02:15

Integer division involved

select datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60.0

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:02:48

Damn! 0.1 minute slow

Peter Larsson
Helsingborg, Sweden

pootle_flump

1064 Posts

Posted - 2007-07-06 : 11:03:55

quote:
Originally posted by Peso

Damn! 0.1 minute slow

1 * 0.1 minutes too slow

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-07-06 : 11:08:20

quote:
Originally posted by Peso

Integer division involved

select datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60.0

Peter Larsson
Helsingborg, Sweden

It returns
-.250000

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:11:55

If you want the difference in minutes, why are you dividing by 60 when you have told the datediff function to return minutes in the first place?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:12:41

select convert(char(5), dateadd(mi, datediff(mi, '01/01/2007 01:30:00', '01/01/2007 01:45:00'), 0), 108)

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 11:13:05

quote:
Originally posted by mahesh_bote

quote:
Originally posted by Peso

Integer division involved

select datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60.0

Peter Larsson
Helsingborg, Sweden

It returns
-.250000

Mahesh

What's wrong with that ? It gives you the correct result

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-06 : 11:13:57

Double DAMN !

KH
[spoiler]Time is always against us[/spoiler]

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-07-06 : 11:18:33

quote:
Originally posted by khtan

quote:
Originally posted by mahesh_bote

quote:
Originally posted by Peso

Integer division involved

select datediff(mi, '01/01/2007 01:45:00', '01/01/2007 01:30:00') / 60.0

Peter Larsson
Helsingborg, Sweden

It returns
-.250000

Mahesh

What's wrong with that ? It gives you the correct result

KH
[spoiler]Time is always against us[/spoiler]

Ya, it returns the correct ans n i applied

select (datediff(mi, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60.0) * 60

I m sry Guys.

But can anybody tell me, Datediff should return 0.15 as difference, right? Then why its returning 0 [Zero] instead of 0.15. Is there any setting?

thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 11:21:45

Why are you dividing by 60.0 and then multiplying by 60?
Get rid of the division and multiplication...

select datediff(mi, '01/01/2007 01:30:00', '01/01/2007 01:45:00')

or, if you want higher resulution try this

select datediff(second, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60.0 -- to get minutes out of seconds

Peter Larsson
Helsingborg, Sweden

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-07-06 : 11:31:10

Thanks Peso,

It helps me a lot. By the way these two select statments shows diff o/ps

select datediff(second, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60

select datediff(second, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60.0

for 2nd selecte statement, why it shows the exact o/p when we divide in such a way?

thanks,

Mahesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 13:28:22

Beacuse of integer division.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-06 : 23:18:32

quote:
Originally posted by mahesh_bote

Thanks Peso,

It helps me a lot. By the way these two select statments shows diff o/ps

select datediff(second, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60

select datediff(second, '01/01/2007 01:30:00', '01/01/2007 01:45:00') / 60.0

for 2nd selecte statement, why it shows the exact o/p when we divide in such a way?

thanks,

Mahesh

Becuase both the datatypes are INT, when you divide them, it is converted to INT

Run these

Select 1/2
Select 1/2.0

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources