November 16, 2009 at 5:40 am
I have postdate in as smaldatetime and I need to calculate post_time for PHPBB application. post_time should display the postdate.
To convert the date to int I use the following tsql :
declare @stdt as varchar(20)
set @stdt = '12/31/1969 07:00 pm'
update topic set
topic_time = datediff(ss,@stdt,cast(postdate as datetime))
But for my surprice I get the datediff , but it is + 10.5 hrs then the actual postdate in the table.
Where Iam missing any thing? Please help !!:w00t:
Cheers,
Got an idea..share it !!
DBA_Vishal
November 16, 2009 at 5:54 am
Can you post the table structure and sample date? Help us to help you.
-Vikas Bindra
November 16, 2009 at 7:34 am
vikas bindra (11/16/2009)
Can you post the table structure and sample date? Help us to help you.
PLease find the table struct attached with sample date
Cheers,
Got an idea..share it !!
DBA_Vishal
November 16, 2009 at 7:47 am
-- in appliction this postdate is displayed as Apr 16, 2009 4:29 am
Can you please share the calculation/query that is used by the Application to get the data from phpbb_posts table?
It seems there is some dateadd function used some where.
-Vikas Bindra
November 16, 2009 at 8:25 am
Explanation and test data are completely unclear!! but still tried my best.
It works just fine with this when I retrieve the data after update. What is the problem.
drop table #t
create table #t ( posttime int, postdate smalldatetime)
INSERT INTO #t VALUES (null,'2009-04-15 17:59:16.000')
Select * from #t
declare @stdt as varchar(20)
set @stdt = '12/31/1969 07:00 pm'
update #t set
posttime = datediff(ss,@stdt,cast(postdate as datetime))
Select dateadd(ss, posttime, '12/31/1969 07:00 PM') from #t
---------------------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply