RE: Separating Date and Time in T-SQL
Reading Erik Porter's post Separating Date and Time in T-SQL, I remembered I have created some SQL Server 2000 functions which deals with such scenario. I am attaching them just below for readers' appreciation.
CREATE FUNCTION dbo.DATEVALUE
(
@Datetime datetime
)
/*******************************************************************************
* AUTHOR: Luciano Evaristo Guerche *
*******************************************************************************/
RETURNS datetime
AS
BEGIN
RETURN CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime)
END
GO
CREATE FUNCTION dbo.TIMEVALUE
(
@Datetime datetime
)
/*******************************************************************************
* AUTHOR: Luciano Evaristo Guerche *
*******************************************************************************/
RETURNS datetime
AS
BEGIN
RETURN (@Datetime - CAST(ROUND(CAST(@Datetime AS float), 0, 1) AS datetime))
END
GO
CREATE FUNCTION dbo.DATESERIAL
(
@YearSerial int,
@MonthSerial int,
@DaySerial int
)
RETURNS datetime
AS
BEGIN
RETURN (CAST(CAST(@YearSerial AS varchar(4)) + '-' + CAST(@MonthSerial AS varchar(2)) + '-' + CAST(@DaySerial AS varchar(2)) AS datetime))
END
GO
CREATE FUNCTION dbo.TIMESERIAL
(
@HOUR AS int,
@MINUTE AS int,
@SECOND AS int,
@MILLISECOND AS int
)
/*******************************************************************************
* AUTHOR: Luciano Evaristo Guerche *
*******************************************************************************/
RETURNS datetime
AS
BEGIN
RETURN ((@HOUR / 24) + DATEADD(hour, @HOUR % 24, DATEADD(minute, @MINUTE, DATEADD(second, @SECOND, DATEADD(millisecond, @MILLISECOND, 0)))))
END
GO
CREATE FUNCTION dbo.DATETIME_CUSTOM_FORMAT
(
@INPUT AS datetime = NULL
)
/*******************************************************************************
* AUTHOR: Luciano Evaristo Guerche *
*******************************************************************************/
RETURNS varchar(20)
AS
BEGIN
RETURN (CASE WHEN (ROUND(CAST(@INPUT AS float), 0, 1) * 24) + DATEPART(hour, @INPUT) < 10 THEN '0' ELSE '' END + CAST((ROUND(CAST(@INPUT AS float), 0, 1) * 24) + DATEPART(hour, @INPUT) AS varchar(10)) + ':' +
RIGHT('00' + CAST(DATEPART(minute, @INPUT) AS varchar(2)), 2) + ':' +
RIGHT('00' + CAST(DATEPART(second, @INPUT) AS varchar(2)), 2) + '.' +
RIGHT('000' + CAST(DATEPART(millisecond, @INPUT) AS varchar(3)), 3)
)
END
GO