date format - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

date format

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-11 : 03:33:14

Hello,
I am getting the hours:minutes:seconds i.e. hh:mm:ss of time by the following sql
LEFT(RIGHT(CONVERT(VARCHAR(23), DATEADD(SECOND, intervalField, 0), 121), 12),8)

Question:
How do I get the number of days as well i.e.
days:hh:mm:ss

Thank you

Sachin.Nand

2937 Posts

Posted - 2011-01-11 : 07:59:40

What do you mean by "number" of days ?

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-11 : 08:08:01

for example:
26:45:32 --> hh:mm:ss
should show something like:
01:02:45:32 --> days:hours:minutes:seconds

basically it checks to see if the hours >= 24 hours and then convert to days...

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-11 : 08:50:14

You can start with this
declare @str varchar(30)
set @str = '26:45:32'

select convert(int,left(@str,2))/24 ,convert(int,left(@str,2)) % 24

Jim

Everyday I learn something that somebody else already knew

unfiresniper
Starting Member

5 Posts

Posted - 2011-01-11 : 22:31:20

I noted you were using an "intervalField" to store the seconds.
So, i guess may be you can do it like this:
select right('00' + cast(intervalField /(24*60*60) as nvarchar(2)), 2) + ':'
+ right('00' + cast(intervalField /(60*60)%24 as nvarchar(2)), 2) + ':'
+ right('00' + cast(intervalField /60%60 as nvarchar(2)), 2) + ':'
+ right('00' + cast(intervalField %60 as nvarchar(2)), 2)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-12 : 04:22:14

quote:
Originally posted by jimf

You can start with this
declare @str varchar(30)
set @str = '26:45:32'

select convert(int,left(@str,2))/24 ,convert(int,left(@str,2)) % 24

Jim

Everyday I learn something that somebody else already knew

how do I make sure there is a leading 0 if there is only one digit?
i.e.
convert(int,left(@str,2)) % 24
may give 2 but I would like to see 02 and if it gives two digits such as 12 then there is no need for a leading 0
Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 04:30:27

quote:
Originally posted by jimf

You can start with this
declare @str varchar(30)
set @str = '26:45:32'

select convert(int,left(@str,2))/24 ,convert(int,left(@str,2)) % 24

Jim

Everyday I learn something that somebody else already knew

It wont work if the value for Hrs is more than double figures.

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-12 : 04:46:47

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by jimf

You can start with this
declare @str varchar(30)
set @str = '26:45:32'

select convert(int,left(@str,2))/24 ,convert(int,left(@str,2)) % 24

Jim

Everyday I learn something that somebody else already knew

It wont work if the value for Hrs is more than double figures.

PBUH

hi,
This is fine because I can get the position of the : and take care of it...
The main thing I am trying to solve is:
how do I make sure there is a leading 0 if there is only one digit?
i.e.
convert(int,left(@str,2)) % 24
may give 2 but I would like to see 02 and if it gives two digits such as 12 then there is no need for a leading 0
Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 05:18:43

declare @str varchar(30)
set @str = '48:00:00'

select right('0' + convert(varchar(20),convert(int,left(@str,2))/24),2)

PBUH

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-01-12 : 06:03:58

Thank you all.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-16 : 17:52:21

SELECT CAST(IntervalField / 86400 AS VARCHAR(12)) + ':' + CONVERT(CHAR(8), DATEADD(SECOND, IntervalField % 86400, 0), 8)