these are the functions that I wrote for datederia, timeserial and datetimeserial
I hope useful
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