Technical Article

DateTimeSerial

,

ufnDateTimeSerial returns the smalldatetime for year, month, day, hour, minute

ufnDateSerial returns the smalldatetime for year, month, day

ufnTimeSerial returns the smalldatetime for hour, minute (based on 1st january 1900)

ufnDateTimeSerial (year, month, day, hour, minute) is equivalent to ufnDateSerial (year, month, day) + ufnTimeSerial (hour, minute)

ufnStrCat and ufnStrRAlign are support utilities

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

Rate

3.4 (5)

Share

Share

Rate

3.4 (5)