Converting unix time (PLEASE HELP!)

Oh please put me out of my misery and help me … I am pulling dates from my database which are in unix time. This means the time is displayed as seconds since 01/01/1970. I am trying to convert this to a date so I can create parameters for my report.

I have tried using convert and dateadd but nothing seems to work.

Any ideas?

Have you triedthis

Converting seconds to HHMMSS

My code calculates a duration of a start and end dates. It then converts the
duration into "
HHMMSS" format. My sample returns 0:0:3 which means 3 seconds.
Can someone help me modify my code so that the result would be 0:0:03 and
add the extra "padding 0" when the hours, minutes, or seconds are 1 digit?
CODE **************
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select rtrim(@.duration/3600) + ':' + rtrim(@.duration % 3600/60) + ':' +
rtrim(@.duration
% 60)does this work for you?
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 01:39:14'
set @.dtEndDate = '20060314 01:39:17'
with 2 zeros
select convert(varchar,convert(datetime,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
one zero
select replace(convert(varchar,convert(datetime
,
dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
)),108),'00','0')
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||that'll do it. thanks.
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
> does this work for you?
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 01:39:14'
> set @.dtEndDate = '20060314 01:39:17'
> with 2 zeros
> select convert(varchar,convert(datetime,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101' )),108)
> one zero
> select replace(convert(varchar,convert(datetime
,
> dateadd(s,datediff(s,@.dtStartDate,@.dtEnd
Date),'19000101'
> )),108),'00','0')
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Scott,
Try using the function "right".
select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60),
2)
Be careful with the solution posted by SQL, It does not seem to work for
duration greater than 24 hours.
AMB
"Scott" wrote:

> that'll do it. thanks.
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1145902342.424509.319660@.y43g2000cwc.googlegroups.com...
>
>|||> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
Neither this one when the duration is greater than or equal to 100 hours. we
need to get more than two digits.

> select right('00' + rtrim(@.duration/3600), 2) <--
AMB
"Alejandro Mesa" wrote:
> Scott,
> Try using the function "right".
> select right('00' + rtrim(@.duration/3600), 2) + ':' + right('00' +
> rtrim(@.duration % 3600 / 60), 2) + ':' + right('00' + rtrim(@.duration % 60
),
> 2)
> Be careful with the solution posted by SQL, It does not seem to work for
> duration greater than 24 hours.
>
> AMB
> "Scott" wrote:
>