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