datediff2

Hi, I need to make a date difference in hours
I have a function, there are declared parameters (datetime type) but the
difference is still in a different type (even if its declared as int)
.....
declare @.date1 as datetime --system date, time
declare @.date2 as datetime
declare @.dif as int
set @.dif = (select datediff (hh,@.date1, @.date2)
return @.dif
end
I get 1905-06-02 00:00:00.000
instead of amount of hours
thanks a lotYou need to give us more to go on. Below is the code you posted,, with some
minor adjustments to
make it compile. It returns an integer (24):
declare @.date1 as datetime --system date, time
declare @.date2 as datetime
declare @.dif as int
SET @.date1 = getdate()
SET @.date2 = getdate() + 1
set @.dif = (select datediff (hh,@.date1, @.date2))
SELECT @.dif
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Zuska" <Zuska@.discussions.microsoft.com> wrote in message
news:B211801A-1891-4DF9-8FE9-E39977D8CF99@.microsoft.com...
> Hi, I need to make a date difference in hours
> I have a function, there are declared parameters (datetime type) but the
> difference is still in a different type (even if its declared as int)
> .....
> declare @.date1 as datetime --system date, time
> declare @.date2 as datetime
> declare @.dif as int
> set @.dif = (select datediff (hh,@.date1, @.date2)
> return @.dif
> end
> I get 1905-06-02 00:00:00.000
> instead of amount of hours
> thanks a lot|||Can you provide the complete code? This givs me correct result:
declare @.date1 as datetime --system date, time
declare @.date2 as datetime
declare @.dif as int
set @.date1 = getdate()
set @.date2 = getdate() - 1
set @.dif = (select datediff (hh,@.date1, @.date2))
select @.dif
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
"Zuska" <Zuska@.discussions.microsoft.com> wrote in message
news:B211801A-1891-4DF9-8FE9-E39977D8CF99@.microsoft.com...
> Hi, I need to make a date difference in hours
> I have a function, there are declared parameters (datetime type) but the
> difference is still in a different type (even if its declared as int)
> .....
> declare @.date1 as datetime --system date, time
> declare @.date2 as datetime
> declare @.dif as int
> set @.dif = (select datediff (hh,@.date1, @.date2)
> return @.dif
> end
> I get 1905-06-02 00:00:00.000
> instead of amount of hours
> thanks a lot|||Tibor,
Why are you using the + operator in your example? I used -. Must be
connected with personality - you must be the positive one:-)
Dejan Sarka
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eJazaC5HGHA.3896@.TK2MSFTNGP15.phx.gbl...
> You need to give us more to go on. Below is the code you posted,, with
> some minor adjustments to make it compile. It returns an integer (24):
> declare @.date1 as datetime --system date, time
> declare @.date2 as datetime
> declare @.dif as int
> SET @.date1 = getdate()
> SET @.date2 = getdate() + 1
> set @.dif = (select datediff (hh,@.date1, @.date2))
> SELECT @.dif
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Zuska" <Zuska@.discussions.microsoft.com> wrote in message
> news:B211801A-1891-4DF9-8FE9-E39977D8CF99@.microsoft.com...
>|||thanks for your help!!!
here is my function
CREATE function dbo.myfunc_test
(@.vz int,
@.rok int,
@.ad datetime
)
returns datetime
as
begin
declare @.date as datetime
declare @.time as datetime
declare @.dt as datetime
declare @.dif as int
set @.date = ( select pol1 from vzorky_osr where kod=@.vz and rok=@.rok )
set @.time = ( select pol2 from vzorky_osr where kod=@.vz and rok=@.rok )
set @.dt = ( select dateadd (ss, datediff (ss, '19000101', @.time), @.date))
set @.dif = (select datediff (hh,'dt','ad'))
return @.dif
end
I really dont know how to get difference in hours between these two dates
Tibor Karaszi p_?e:

> You need to give us more to go on. Below is the code you posted,, with som
e minor adjustments to
> make it compile. It returns an integer (24):
> declare @.date1 as datetime --system date, time
> declare @.date2 as datetime
> declare @.dif as int
> SET @.date1 = getdate()
> SET @.date2 = getdate() + 1
> set @.dif = (select datediff (hh,@.date1, @.date2))
> SELECT @.dif
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Zuska" <Zuska@.discussions.microsoft.com> wrote in message
> news:B211801A-1891-4DF9-8FE9-E39977D8CF99@.microsoft.com...
>|||> I really dont know how to get difference in hours between these two dates
... and I still don't know what are you up to. You will have to give us the
Create table statement and some Insert statements with demo data.
BTW, the line
set @.dif = (select datediff (hh,'dt','ad'))
is wrong. I guess it should be
set @.dif = (select datediff (hh,@.dt,@.ad))
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message|||Ive finally made it work.
Thanks for your help, it was my first touch with sql...so...
thanks again
nice evening
Dejan Sarka p_?e:

> ... and I still don't know what are you up to. You will have to give us t
he
> Create table statement and some Insert statements with demo data.
> BTW, the line
> set @.dif = (select datediff (hh,'dt','ad'))
> is wrong. I guess it should be
> set @.dif = (select datediff (hh,@.dt,@.ad))
> --
> Dejan Sarka, SQL Server MVP
> Mentor, www.SolidQualityLearning.com
> Anything written in this message represents solely the point of view of th
e
> sender.
> This message does not imply endorsement from Solid Quality Learning, and i
t
> does not represent the point of view of Solid Quality Learning or any othe
r
> person, company or institution mentioned in this message
>
>|||:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage
news:uuKnOF5HGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Tibor,
> Why are you using the + operator in your example? I used -. Must be connec
ted with personality -
> you must be the positive one:-)
> --
> Dejan Sarka
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eJazaC5HGHA.3896@.TK2MSFTNGP15.phx.gbl...
>|||On Sun, 22 Jan 2006 12:36:01 -0800, Zuska wrote:
(snip)
>returns datetime
(snip)
>declare @.dif as int
(snip)
>return @.dif
Hi Zuska,
Since you specified that the function has to return a datetime, the
integer value in @.dif will be implicitly converted to datetime when the
RETURN statement is executed. The result of this implicit value will be
(@.dif) days after Jan 1st, 1900, at exactly midnight.
To make it return an integer, simply change the RETURNS clause of the
CREATE FUNCTION statement to:
RETURNS int
Hugo Kornelis, SQL Server MVP