DateTimeSerial

CREATE FUNCTION dbo.ufnDateTimeSerial (@Year int=null, @Month int=null, @Day int=null, @Hour int=null, @Minute int=null)  
/*to get the date pass year, month and day*//*to get the time pass hour and minute*/RETURNS smalldatetime AS  
BEGIN 

DECLARE @strDate varchar(8)
DECLARE @strTime varchar(5)

IF @Year IS NULL OR @Month IS NULL OR @Day IS NULL
SELECT @strDate = ''
ELSE
SELECT @strDate = dbo.ufnStrRAlign(CAST(@Year AS int), 4, '0') + dbo.ufnStrRAlign(CAST(@Month AS int), 2, '0') + dbo.ufnStrRAlign(CAST(@Day 

AS int), 2, '0')

IF @Hour IS NULL OR @Minute IS NULL 
SELECT @strTime = ''
ELSE
SELECT @strTime =  dbo.ufnStrRAlign(CAST(@Hour AS int), 2, '0') + ':' +  dbo.ufnStrRAlign(CAST(@Minute AS int), 2, '0')

RETURN CAST(dbo.ufnStrCat (' ', @strDate, @strTime, 0) AS smalldatetime)
END


CREATE FUNCTION dbo.ufnDateSerial (@Year int, @Month int, @Day int)  
RETURNS smalldatetime AS  
BEGIN 
RETURN dbo.ufnDateTimeSerial (@Year, @Month, @Day, null, null)
END


CREATE FUNCTION dbo.ufnTimeSerial (@Hour int, @Minute int)  
RETURNS smalldatetime AS  
BEGIN 
RETURN dbo.ufnDateTimeSerial (null, null, null,@Hour, @Minute)  
END

CREATE FUNCTION dbo.ufnStrRAlign
(@In varchar(8000), @OutLen integer, @strFiller varchar(1)=' ')
RETURNS varchar(8000)  AS  
BEGIN
DECLARE @Result varchar(8000)
DECLARE @FillLen integer

SELECT @FillLen =@OutLen-LEN(ISNULL(@In, ''))

IF @FillLen > 0
SELECT @Result = REPLICATE(@strFiller, @FillLen) + ISNULL(@In, '')
ELSE
SELECT @Result = ISNULL(@In, '')


RETURN @Result 
END

 
CREATE FUNCTION dbo.ufnStrCat
(@Separator varchar(8000), @Str1 varchar(8000), @str2 varchar(8000), @Distinct smallint)

RETURNS varchar(8000)  AS  
BEGIN
DECLARE @Result varchar(8000)

IF ( @Distinct<>0 AND ISNULL(@Str1, '') = ISNULL(@Str2, ''))
SELECT @Result=ISNULL(@Str1, '')
ELSE
BEGIN
IF @Str1 IS NOT NULL SELECT @Result=@Str1

IF (LEN (LTRIM(ISNULL(@Result, '')))>0 AND LEN (LTRIM(ISNULL(@Str2, '')))>0) SELECT @Result=@Result + @Separator

IF @Str2 IS NOT NULL SELECT @Result=ISNULL(@Result, '') + @Str2 
END

RETURN @Result 
END