November 16, 2009 at 1:09 pm
This is a sample, but I have the following:
declare @thisTime decimal(8,2)
set @thisTime = datediff(hh, getdate(), '11/22/2009')/8
select @thisTime
This returns 16.00, but I really need it to also return the decimals with the value. Every value I try I am getting .00. Is this because of getdate()? I need to get the current date and time.
Thanks for the information.
November 16, 2009 at 1:11 pm
DateDiff returns an integer value. It won't give you fractions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2009 at 1:16 pm
Is there a way to get fractions?
November 16, 2009 at 1:17 pm
Yes. Depends on what you want.
For example, if it's been 1 hour and 30 minutes, are you looking for 1.30 or for 1.5?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2009 at 1:33 pm
You could also do this:
select left(convert(varchar, getdate(), 108),5)
Never mind this. Again, brain dead for a bit, misread the original query.
November 16, 2009 at 1:54 pm
This will give you an approximate answer:
declare @thisTime decimal(8,2)
declare @thisTime2 decimal(8,2)
set @thisTime = datediff(ss, getdate(), '11/22/2009')
set @thisTime2 = CAST(datediff(ss, getdate(), '11/22/2009')AS DECIMAL(8,2))/3600
select @thisTime AS 'Total seconds' ,@thisTime2 AS 'Total hours', @thisTime2*3600,
(@thisTime2*3600) - @ThisTime AS 'Approximation error in seconds'
At the time I ran this, it resulted in:
Total seconds 461498.00 -- showing results step by step
Total hours 128.19--this is the answer you requested
Go back461484.00 --to determine how much of an approximation
Approximation error in seconds -14.00
Now is that is not accurate enough you could use the rounding function
declare @thisTime decimal(8,2)
declare @thisTime2 decimal(8,3) --note change
set @thisTime = datediff(ss, getdate(), '11/22/2009')
set @thisTime2 = CAST(datediff(ss, getdate(), '11/22/2009')AS DECIMAL(8,2))/3600
select @thisTime AS 'Total seconds' ,@thisTime2 AS 'Total hours', @thisTime2*3600 AS 'Go back',
(@thisTime2*3600) - @ThisTime AS 'Approximation error in seconds',ROUND(@thistime2, 2) AS 'Rounded'
,CAST(ROUND(@thistime2, 2) AS DECIMAL(8,2)) aS 'Lesser error'
The approximation error for the above runs a little below 2 seconds.
November 16, 2009 at 2:22 pm
I'm not saying this gives you correct results, but integer math returns integer values. Try changing the 8 to 8.0:declare @thisTime decimal(8,2)
set @thisTime = datediff(hh, getdate(), '11/22/2009')/8.0
select @thisTime
November 16, 2009 at 3:28 pm
Thank you - this actually did it!
declare @thisTime decimal(8,2)
set @thisTime = datediff(hh, getdate(), '11/22/2009')/8.0
select @thisTime
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply