7. April 2008 by admin.

Here is another handy date manipulation function. This one converts the number of seconds, expressed as an int, into the HH:MM:SSS format

SELECT
CASE
WHEN @seconds/3600<10 THEN '0'
ELSE ”
END
+ RTRIM(@seconds/3600)
+ ':' + RIGHT('0'+RTRIM((@seconds % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((@seconds % 3600) % 60),2)

Rewritten as a function, we have:

CREATE FUNCTION SecToHHMMSS
(
– Add the parameters for the function here
@seconds INT
)
RETURNS VARCHAR(10)
AS
BEGIN
RETURN
CASE
WHEN @seconds/3600<10 THEN '0'
ELSE ”
END
+ RTRIM(@seconds/3600)
+ ‘:’ + RIGHT(‘0′+RTRIM((@seconds % 3600) / 60),2)
+ ‘:’ + RIGHT(‘0′+RTRIM((@seconds % 3600) % 60),2)
END
GO


Posted in T-SQL | Print | No Comments »

7. April 2008 by Bennett.

Here is a very simple and elegant way to strip the time component from a datetime value:

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

Written as a function:

CREATE FUNCTION BareDate
(
– Add the parameters for the function here
@date datetime
)
RETURNS datetime
AS
BEGIN
RETURN CAST(FLOOR(CAST(@date AS float)) AS datetime)
END
GO

When we pass in the value ‘2008-02-12 13:25:33.3′, it returns the value 2008-02-12 00:00:00.000

Other ways of stripping the time component:

SELECT DATEADD(dd,(DATEDIFF(dd,0,backup_start_date)),0)

or:

SELECT CAST(DATEDIFF(dd,0,backup_start_date) AS Datetime)Using the convert has performance issues.  I’ll have to come up with numbers for all these: or

SELECT CONVERT(Datetime, CONVERT(NCHAR(10), backup_start_date, 121))

Posted in SQL Server | Print | No Comments »