convert ms sql datetime 2000 to a PHP / UNIX time stamp

I found this function on the web but being in the UK it doesn' t convert the time correctly

for example

Expr1      SLAContract_StartDate      Expr1      SLAContract_EndDate
1246406400      01/07/2009      1277942399      30/06/2010 23:59:59
1243814400      01/06/2009      1269993600      31/03/2010

should be according to our own php checker
                  
Date String timestamp: 1277938799
Date String timestamp check: 30/06/2010 11:59pm

but if I put 1277942399 it converts to the next day
Timestamp date: 01/07/2010 00:59:59

we are in England UK London

CREATE FUNCTION dbo.DTtoUnixTS
(
@dt DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @diff BIGINT
IF @dt >= 203801192
BEGIN
SET @diff = CONVERT(BIGINT, DATEDIFF(S, 197001012, 203801192))
+ CONVERT(BIGINT, DATEDIFF(S, 203801192, @dt))
END
ELSE
SET @diff = DATEDIFF(S, 197001012, @dt)
RETURN @diff
END

Open in new window

Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Here you go... you looked in the wrong place, has been here all along :)



 
 
/*
Expr1      SLAContract_StartDate      Expr1      SLAContract_EndDate
1246406400      01/07/2009      1277942399      30/06/2010 23:59:59
1243814400      01/06/2009      1269993600      31/03/2010
*/
 
-- sample usage. Either param1 = date or param2 = unix , param3 = units (some unix times are in milliseconds)
-- e.g. unix date to normal sql date
 
select dbo.uUnixDate(NULL,1277942399,1)
 
-- e.g. normal date to unix
 
select dbo.uUnixDate('20090601',NULL,1)
 
-- now the function :
 
create function uUnixDate(@dt datetime,@unix bigint,@uom int)
returns varchar(30)
as
begin
 
if @unix is NULL return convert(varchar,convert(bigint,((convert(decimal(36,18),@dt) - 25567) * 60 * 60 * 24 * @uom)))
if @dt is NULL return replace(convert(varchar,convert(datetime,((@unix / (@uom * 60.0 * 60.0 * 24.0)) + 25567)),121),'-','')
 
return NULL
 
end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.