Time Question Decimal 4,0 to Time hhmmss

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Time Question Decimal 4,0 to Time hhmmss

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2011-06-16 : 08:56:40

I have the coding from a question prior that was from a decimal 6,0 to time. BUt I havent figured the proper math to get it to work for a 4,0

The field is TIME1

CONVERT(VARCHAR (8),(CAST(DATEADD(SECOND, FLOOR(Time1/ 10000) * 3600 + FLOOR(Time1 / 100) % 100 * 60 + Time1 % 100, 0) AS DATETIME)) ,108)

With this field... Time1 Currently is 2152

This code translates to 00:21:52

It should show 21:52:00

Any help would be greatly appriciated

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-16 : 09:04:20

declare @Time1 decimal(4,0)
set @Time1 = 2152

select
CONVERT(VARCHAR (8),(CAST(DATEADD(SECOND, FLOOR(@Time1/ 100) * 3600 + FLOOR(@Time1) % 100 * 60 , 0) AS DATETIME)) ,108)

No, you're never too old to Yak'n'Roll if you're too young to die.

ZMike
Posting Yak Master

110 Posts

Posted - 2011-06-16 : 09:07:21

webfred,

That did it.... Thank you very much. I think that's the fastest responce I've ever recieved on here !

ZMike
Posting Yak Master

110 Posts

Posted - 2011-06-16 : 10:12:16

Sorry Webfred one more question please. I want to also translate it into a date time

Now that the time works great

I have this... it's close but not quite right

DATEADD(HOUR, FLOOR( Time1/ 100), DATEADD(MINUTE, FLOOR( Time1/ 1) % 100, DATEADD(SECOND, Time1 % 100, Date1)))

Date Should = 2011-06-05
Time should = 01:16:00

togeather should be 2011-06-05 01:16:00.000

I come back with 2011-06-05 02:32:16.000

It seems like it works most of the time but sometimes it misses.

ZMike
Posting Yak Master

110 Posts

Posted - 2011-06-16 : 11:04:55

I'm not sure if this is the best way but it seems to work

CAST( Date1 + CONVERT(VARCHAR (8),(CAST(DATEADD(SECOND, FLOOR(Time1/ 100) * 3600 + FLOOR(Time1) % 100 * 60 , 0) AS DATETIME)) ,108) AS DATETIME)

Exgliderpilot
Starting Member

14 Posts

Posted - 2011-06-17 : 04:36:29

this is not quite what you want but it has produced the correct answeres for years now

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create function [dbo].[af_CastMinsAsTimeString]
(@ParamMins smallint)
RETURNS Varchar(5)
AS
BEGIN
DECLARE @TimeString varchar(5)
DECLARE @StartHour smallint
DECLARE @StartMin smallint
SET @StartHour = @ParamMins / 60
SET @StartMin = @ParamMins % 60
SET @TimeString =
CASE
WHEN @StartHour < 10 THEN ' ' + CAST(@StartHour As Char(1))
ELSE CAST(@StartHour As Char(2))
END
+ ':' +
CASE
WHEN @StartMin < 10 THEN '0' + CAST(@StartMin As Char(1))
ELSE CAST(@StartMin As Char(2))
END
RETURN @TimeString
END

GO

I'm sure its easy to extend to your exact requirments

Staff bank ageny software http:\\www.ava.co.uk
Blog
http://creamteadiet.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources