views:

1055

answers:

4

I need to convert a field with milliseconds created by PHP app to a date value. Is there a way to do this using vbcript? or convert to datetime in SQL 2005

Example: 1113192000 to mm/dd/yyyy hh:mm:ss

Thanks,

Sam

+1  A: 

You'll need to have a base time to count the milliseconds from e.g. 1st Jan 1970 or similar.

You then divide the number of milliseconds by 1000 to get the number of seconds - saving the remainder.

Divide the number of seconds by 60 (saving the remainder) to get the number of minutes.

Then 60 again for hours, 24 for days.

Then it get's difficult as you've got leap years to consider. There is another question on this here.

Once you've got your years, days, hours, minutes, seconds and milliseconds you add this to the base date-time to get the date-time represented.

Others have posted code etc. that you might use.

ChrisF
OK. What if the base time is Jan 1, 1970. What would be the code to convert it? Thanks.
thank you very much.
+1  A: 

I think what you are referring to as milliseconds is really the epoch time as returned by the php's time/date functions. You can give this a function a shot to get the epoch time converted to datetime format in ASP:

function epoch2date(myEpoch)

epoch2date = DateAdd("s", myEpoch, "01/01/1970 00:00:00")

end function

Source: http://www.epochconverter.com/epoch/functions.php#asp

Sachin
+3  A: 

Something like the following should work:

Function ConvertPhpToDate(numSeconds)
  Dim dEpoch
  dEpoch = DateSerial(1970,1,1)  
  ConvertPhpToDate = DateAdd("s",numSeconds,dEpoch) 
End Function

Note, the php time() function returns the number of 'seconds', not milliseconds. http://php.net/manual/en/function.time.php

samjudson
Thanks this worked for me.
+1  A: 
msValue = 32312312
dtValue = DateAdd("s", msValue/1000, CDate("1970-01-01 00:00:00"))

Wrap it in a function:

Function TimestampToDate(timestamp)
  TimestampToDate = DateAdd("s", timestamp/1000, CDate("1970-01-01 00:00:00"))
End Function
Tomalak