Saving Datetime to variable

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
On Sun, 22 Jan 2006 06:14:02 -0800, pietro wrote:

>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
Hi Pietro,
Are these date and time columns stored as datetime or as char/varchar?
If the former, you might consider storing them together in one column.
If the latter, you might consider storing them as datetime (much better
integrity checks, much better possibilities for doing calculations) AND
consider storing them together in one column.

>CREATE function dbo.dej_rozdil (@.vz int, @.r int) returns char(100)
Why are you not returning a datetime? That's what you want to store the
combined value in, after all!

>as
>begin
>declare @.in_date as varchar
>declare @.in_time as varchar
Since you don;t specify length here, these variables are now defined at
the default length: varchar(1).

>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 )
The use of convert in this statement suggests that the columns are
already of the datetime datatype. All this converting from datetime to
string and back won't do your performance any good - and the fact that
you didn't choose one of the recommended formats means that you're not
even assured that this will never return unexpected results.
See below for some better suggestions.

>set @.in = @.in_date +' '+ @.in_time
>return @.ret
>end
Your choice to use a user-defined functions has some performance
implications as well. I am aware of the benefits of UDF for readability,
reuseability and documentation - but are you aware of the implications
on performance?
Anyway, I promised you some alternatives. The first alternative is what
you should use if pol1 and pol2 are defined as datetime or smalldatetime
columns:
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT DATEADD(ms, DATEDIFF(ms, '0:00', pol2), pol1)
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
The second alternative is what you should use if ALL of the following
conditions are met:
* pol1 and pol2 are both char or varchar
* pol1 is formatted yyyy-mm-dd
* pol2 is formatted hh:mm:ss or hh:mm:ss.mmm (where .mmm denotes the
milliseconds)
DECLARE @.result datetime -- or smalldatetime
SET @.result = (SELECT CAST(pol1 + 'T' + pol2 AS datetime) -- or
smalldatetime
FROM dbo.vzorky_osr
WHERE kod = @.vz
AND rok = @.r)
If col1 and col2 are (var)char but not in the format noted above, you
can either
a) use string manipulation to build a yyyy-mm-ddThh:mm:ss[.mmm] format
from whatever the current format is, or
b) concatenate them anyway, then use CONVERT with a style parameter to
ensure that SQL Server can't misinterpret the format.
Example for b:
CAST ('3/4/5' AS datetime) is dangerous, because is can be interpreted
as 3rd of april 2005, of march 4, 2005, 2003, april 5 or m,aybe yet
something else
CONVERT(datetime, '3/4/5', 11) is unambitious, since the style parameter
11 says that the japanese format (yy/mm/dd) is used.
If you need further help, then you'll have to provide more information.
See www.aspfaq.com/5006 for that.
Hugo Kornelis, SQL Server MVP