convert datetime

Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
endStart with this and modify accordingly:
create function dbo.MyFunc
(
@.date datetime
, @.time datetime
)
returns datetime
as
begin
return (
select
dateadd (ss, datediff (ss, '19000101', @.time), @.date)
)
end
go
select dbo.MyFunc ('2006-01-22', '19:00')
go
drop function dbo.MyFunc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
Hi im working on a simple function. I have a table with two columns
(datetime) and I need to extract date rom one of them and time from another
one - and then to join it and save it in the variable (which is declered as
a
datetime). Please, can somebody help me or advice another waz how to get it?
many thanks
CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
as
begin
declare @.in_date as varchar
declare @.in_time as varchar
declare @.in as char(100)
set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr where
kod=@.vz and rok=@.r )
set @.in = @.in_date +' '+ @.in_time
return @.ret
end|||Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:

> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||ive tried again, its working nice. Thanks a lot
Tom Moreau p_?e:

> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from anothe
r
> one - and then to join it and save it in the variable (which is declered a
s
> a
> datetime). Please, can somebody help me or advice another waz how to get i
t?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr wher
e
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr whe
re
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>|||All "time-only" datetime's have a date portion of 1900-01-01.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pietro" <pietro@.discussions.microsoft.com> wrote in message
news:959F23F1-45CF-4FC2-B078-87A4651C8DD1@.microsoft.com...
Hi Tom, thanks a lot, it works perfectly...I have just one more problem I
cant find it out...
in my table, all the values, which I need to use as a time is like 1.1.1900
9:15:00
how can I separate just the time to use it in the function?
Tom Moreau p_?e:

> Start with this and modify accordingly:
> create function dbo.MyFunc
> (
> @.date datetime
> , @.time datetime
> )
> returns datetime
> as
> begin
> return (
> select
> dateadd (ss, datediff (ss, '19000101', @.time), @.date)
> )
> end
> go
> select dbo.MyFunc ('2006-01-22', '19:00')
> go
> drop function dbo.MyFunc
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "pietro" <pietro@.discussions.microsoft.com> wrote in message
> news:5CCA06FC-7672-42AD-BBB0-432AEC1ED1E5@.microsoft.com...
> Hi im working on a simple function. I have a table with two columns
> (datetime) and I need to extract date rom one of them and time from
> another
> one - and then to join it and save it in the variable (which is declered
> as
> a
> datetime). Please, can somebody help me or advice another waz how to get
> it?
> many thanks
> CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
> as
> begin
> declare @.in_date as varchar
> declare @.in_time as varchar
> declare @.in as char(100)
> set @.in_date = ( select convert(varchar,pol1,102) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in_time = ( select convert(varchar,pol2, 108) from dbo.vzorky_osr
> where
> kod=@.vz and rok=@.r )
> set @.in = @.in_date +' '+ @.in_time
> return @.ret
> end
>