Convert time to string - SQL

Scenario where a db field contains seconds, the user wanted a SSRS report displaying various aggregates of the seconds field as Hours - Minutes - Seconds.


IFOBJECT_ID('dbo.udf_SCC_TimeToString') IS NOT NULL
DROP FUNCTION dbo.udf_SCC_TimeToString ;
GO

CREATEFUNCTION dbo.udf_SCC_TimeToString
(
@timesec INT) --input in seconds as integer

RETURNSVARCHAR(25)

/*==========================================

Dom Horton

17/10/2012

==========================================*/

AS

 
BEGIN
DECLARE
@return
AS VARCHAR(25),

@a AS INT,

@b AS INT,

@hours AS INT,

@mins AS INT,

@secs AS INT;

SET@a = @timesec%3600 --get no. of seconds over the hour

SET@hours = (@timesec - @a)/3600 --get no. of seconds of completed hours, divide by hour in seconds to get completed hours

SET@b = @a --hold no. of secs over the hour

SET@a = @b%60--get no. of secs over the min

SET@mins = (@b - @a)/60 --get no. of seconds of completed mins, divide by min in secs to get completed minutes

SET@secs = @a

SET@return =

CASE
 
WHEN @timesec >=3600

THEN
 
convert(varchar(10),@hours) + ' hr ' +

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

WHEN @timesec >=60

THEN

convert(varchar(10),@mins) + ' min ' +

convert(varchar(10),@secs) + ' sec '

ELSE

convert(varchar(10),@secs) + ' sec '

END;

RETURN
@return

END











Example below showing the Stored Procedure in use:

and in use in a SSRS report:

Converting Datetime values to various formats - SQL

Using the CONVERT function in SQL Server 2005 the following datetime formats can be produced:


SELECT
GETDATE() AS UnconvertedDateTimeUsingGetDate,
CURRENT_TIMESTAMP AS UnconvertedDateTimeUsingCurrentTimestamp,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 100) AS [Default mon dd yyyy hh:miAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 101) AS [USA mm/dd/yy],
CONVERT(nvarchar(30), GETDATE(), 102) AS [ANSI yy.mm.dd],
CONVERT(nvarchar(30), GETDATE(), 103) AS [British/French dd/mm/yy],
CONVERT(nvarchar(30), GETDATE(), 104) AS [German dd.mm.yy],
CONVERT(nvarchar(30), GETDATE(), 105) AS [Italian dd-mm-yy],
CONVERT(nvarchar(30), GETDATE(), 106) AS [dd mon yy],
CONVERT(nvarchar(30), GETDATE(), 107) AS [Mon dd, yy],
CONVERT(nvarchar(30), GETDATE(), 108) AS [hh:mm:ss],
CONVERT(nvarchar(30), GETDATE(), 109) AS [Default+milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)],
CONVERT(nvarchar(30), GETDATE(), 110) AS [USA mm-dd-yy],
CONVERT(nvarchar(30), GETDATE(), 111) AS [Japan yy/mm/dd],
CONVERT(nvarchar(30), GETDATE(), 112) AS [ISO yymmdd],
CONVERT(nvarchar(30), GETDATE(), 113) AS [Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 114) AS [hh:mi:ss:mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 120) AS [ODBC canonical yyyy-mm-dd hh:mi:ss(24h)],
CONVERT(nvarchar(30), GETDATE(), 121) AS [ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)],
CONVERT(nvarchar(30), GETDATE(), 126) AS [ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)],
CONVERT(nvarchar(30), GETDATE(), 130) AS [Hijri**** dd mon yyyy hh:mi:ss:mmmAM],
CONVERT(nvarchar(30), GETDATE(), 131) AS [ Hijri**** dd/mm/yy hh:mi:ss:mmmAM]

Auxiliary Calendar table - SQL

This code is derived from a useful article by Todd Fifield at SQLServerCentral.com and has been modified to include columns based on the ISO8601 date and times standard (here and here).

IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO

CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10) 
, DayNumber INT
, NameOfDay VARCHAR(10) 
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO