Solved

Convert SQL datetime to timestamp

Posted on 2004-09-06
6
8,755 Views
Last Modified: 2011-09-20
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!
0
Comment
Question by:maunded
  • 4
  • 2
6 Comments
 
LVL 10

Accepted Solution

by:
imrancs earned 125 total points
Comment Utility
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
 
LVL 1

Author Comment

by:maunded
Comment Utility
Imran my man you are a genius
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
Glad if I could help!!!


Imran
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:maunded
Comment Utility
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
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
yes you are right, this difference is becuase of timezone.

UNIX_TIMESTAMP() actually calculates according to GMT.


Imran
0
 
LVL 10

Expert Comment

by:imrancs
Comment Utility
here is how you can get difference (in hours) from GMT


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


Imran
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now