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

5 Comments

  • Your function do not get the date part of a datetime value. The problem is that you're rounding the value instead of stripping the time value and just keeping the integer part:





    CREATE FUNCTION [dbo].[up_date_value] (@date datetime) RETURNS datetime AS



    RETURN CAST(FLOOR(CAST(@date AS float)) AS datetime)





    Your function will return the next day for values in the afternoon. Mine function would return invalid values for negative dates but SQL server doesn't support those. If it did all we'd have to do is to add an IF there and use CEILING for negative dates.

  • And I look like an idiot, ROUND(value, 0, 1) will truncate, not ROUND :)

  • Dear Jerry,



    Thanks for your comments on the code. As you noted on time, ROUND may also TRUNCATE. I guess my code works properly, at least it worked when I tested it last time. Anyway, I am happy there are people out there watching my scarce postings and spending time commenting on it...

  • Very slick....



    somewhere I saw a snipet one time that would take a row of values and make it output as a set of rows...



    U used that to make a drop-down-combo in html with one sql call to build the whole list.



    /me wonders if I can find that code now?

  • Great solution! Thanks.

Comments have been disabled for this content.