UDF - your comments please - Gerard

Hi,

Because I'm using sp_add_jobschedule in some of my code (stored proc)
I came up with the function below so that a user who wants to add a
job schedule for a given date & time does not have to do the
conversion from date or time to an int.

It may all sound a bit superfluous but not all of our staff here are
well versed at SQL but may need to call on the stored proc (which is
in essence a wrapper around sp_add_job, sp_add_jobschedule etc) from
their own code.

I look forward to your feedback on the workings and structure of the
function and any problems you may spot with it.

thanks.
Gerard


___________________________________________

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object:  User Defined Function dbo.ingrFnDateTimeInt    Script
Date: 20-6-2007 15:37 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[ingrFnDateTimeInt]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ingrFnDateTimeInt]
GO

CREATE FUNCTION ingrFnDateTimeInt (@Date datetime, @ReturnType
char(1))
RETURNS int
AS
BEGIN
/*
This function returns an integer value for either date or time
based
upon the combination of the date and return type provided. The
format
of the integer value is:

* hhmmss, so 07:00:00 returns 7000
* yyyymmdd, so 1971/07/30 returns 19710730

If no valid return type is provided (D or d for date and T or t for
time)
the value returned by this function is zero.
*/
DECLARE
@DateTimeInt int,
@cYear char(4),
@cMonth char(2),
@cDay char(2),
@cHours char(2),
@cMins char(2),
@cSecs char(2)

IF(@ReturnType = 'd' or @ReturnType = 'D')
BEGIN
SET @cYear = RTRIM(CAST(DATEPART(yyyy, @Date) as char(4)))

IF(LEN(RTRIM(CAST(DATEPART(m, @Date) as char(2))))) = 1
BEGIN
SET @cMonth = '0' + RTRIM(CAST(DATEPART(m, @Date) as char(2)))
END
ELSE
BEGIN
SET @cMonth = RTRIM(CAST(DATEPART(m, @Date) as char(2)))
END

IF(LEN(RTRIM(CAST(DATEPART(d, @Date) as char(2))))) = 1
BEGIN
SET @cDay = '0' + RTRIM(CAST(DATEPART(d, @Date) as char(2)))
END
ELSE
BEGIN
SET @cDay = RTRIM(CAST(DATEPART(d, @Date) as char(2)))
END

SET @DateTimeInt = CONVERT(int, @cYear + @cMonth + @cDay)
END
ELSE IF(@ReturnType = 't' or @ReturnType = 'T')
BEGIN
IF(LEN(RTRIM(CAST(DATEPART(hh, @Date) as char(2))))) = 1
BEGIN
SET @cHours = '0' + RTRIM(CAST(DATEPART(hh, @Date) as char(2)))
END
ELSE
BEGIN
SET @cHours = RTRIM(CAST(DATEPART(hh, @Date) as char(2)))
END

IF(LEN(RTRIM(CAST(DATEPART(n, @Date) as char(2))))) = 1
BEGIN
SET @cMins = '0' + RTRIM(CAST(DATEPART(n, @Date) as char(2)))
END
ELSE
BEGIN
SET @cMins = RTRIM(CAST(DATEPART(n, @Date) as char(2)))
END

IF(LEN(RTRIM(CAST(DATEPART(s, @Date) as char(2))))) = 1
BEGIN
SET @cSecs = '0' + RTRIM(CAST(DATEPART(s, @Date) as char(2)))
END
ELSE
BEGIN
SET @cSecs = RTRIM(CAST(DATEPART(s, @Date) as char(2)))
END

SET @DateTimeInt = CONVERT(int, @cHours + @cMins + @cSecs)
END
ELSE
BEGIN
SET @DateTimeInt = 0
END

RETURN(@DateTimeInt)
END

GO

GRANT EXECUTE ON ingrFnDateTimeInt TO DYNGRP

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

UDF - your comments please - Tibor Karaszi

Gerard,

It seems that http://sqldev.net/sqlagent/SQLAgentDateTime.htm does something similar, and might have
some other handy conversions already done for you...?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Or you could use the ones Gert Drapers (high-up in the SQL team at ) - Aaron Bertrand [SQL Server MVP]

Or you could use the ones Gert Drapers (high-up in the SQL team at
Microsoft) wrote for exactly this purpose:
http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Simplifies to something like this... - markc60

Simplifies to something like this...

CREATE FUNCTION ingrFnDateTimeInt (@Date datetime, @ReturnType
char(1))
RETURNS INT
AS
BEGIN
RETURN CASE WHEN @ReturnType='D' THEN CAST(CONVERT(char(8),@Date,112)
AS INT)
WHEN @ReturnType='T' THEN
CAST(REPLACE(CONVERT(char(8),@Date,108),':','') AS INT)
ELSE 0 END
END

Mark, thanks for the insights. - Gerard

Mark, thanks for the insights. It becomes a very sweet little function
like this.

Aaron and Tibor, thanks for the urls

Regards,
Gerard