Convert SQL datetime to timestamp

Hi,
Im having a big problem with yet another simple task :)
Trying to get MS SQL to convert its datetime type to a UNIX timestamp
How can this be done?  I know in MySQL there is the UNIX_TIMESTAMP function, but how does this relate in mssql?
The problem exists becuase I am trying to use PHP to convert the mssql datetime to a unix timestamp, and then back.
If I get a date from the database such as 2004-Sep-03 05:09:00 and use the php function strtotime() on that, it converts it, but then using strftime() converts it back and it ends up being somewhere around 2010!!
Help!
LVL 1
maundedAsked:
Who is Participating?
 
imrancsCommented:
UNIX_TIMESTAMP()

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' GMT) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00'


here is how can you do it in MSSQL

select datediff(second, '1970-01-01 00:00:00','2004-Sep-03 05:09:00')

Imran
0
 
maundedAuthor Commented:
Imran my man you are a genius
0
 
imrancsCommented:
Glad if I could help!!!


Imran
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
maundedAuthor Commented:
Actually that hasnt quite solved my problem I just found out, when I convert the timestamp back in php, its offset by +10hrs (something to do with the fact my timezone is -10 I guess
0
 
imrancsCommented:
yes you are right, this difference is becuase of timezone.

UNIX_TIMESTAMP() actually calculates according to GMT.


Imran
0
 
imrancsCommented:
here is how you can get difference (in hours) from GMT


select datediff(hour,getdate(), getutcdate()) AS GMTDiff


Imran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.