MS SQL unix timestamp to mm/dd/yyyy conversion

I have a field in a database that contains unix timestamps.  I recently discovered that it would be handy to have actual dates in the database for troubleshooting purposes.  I only need mm/dd/yyyy, nothing more detailed.  I added a column for the date and have been running a clumsy lookup/update query using php.  I'm having php do the conversion from unix to actual date.  Unfortunately, it's taking forever.

Is there some way within the contructs of SQL to read the unix timestamp from a column and write the corresponding mm/dd/yyyy date format to another column?
shtaffaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rich WeisslerConnect With a Mentor Professional Troublemaker^h^h^h^h^hshooterCommented:
You have a table that already have a column with some sort of integer value for the UnixTimeStamp.  You've already created a column as a datetime, which I'll call ConvertedDate.

test this first, and backup your database... but you should be able to:

UPDATE <YOURTABLENAME>
  SET <ConvertedDate> = DATEADD(second, <UnixTimeStamp>, {d '1970-01-01'})
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
Borrowing heavily from another source...

DATEADD(second, @timestamp, {d '1970-01-01}) where @timestamp is the value for your unix timestamp?
0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
And as I test in sql 2005... it looks like there is a quote missing, so:
DATEADD(second, <unixtimestampvalue>, {d '1970-01-01'})
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
shtaffaAuthor Commented:
Being only moderately fluent in the language of SQL, would it be possible to get a complete example of how to do what I'm trying to accomplish?
0
 
shtaffaAuthor Commented:
The query you supplied worked really quick.  However, is there a way to format the converted date?  For an example, it added "Oct 1 2011" to the date column.  Is it possible to have it formatted as "10/01/2011"?
0
 
Kevin CrossChief Technology OfficerCommented:
SQL does NOT format the stored DATETIME object. What you are seeing is the result of your default or session language. You can set your language to English and it will default to MM/DD/YYYY display. A DATETIME is a DATETIME, though. And just to elaborate, a UNIX timestamp represents the number of seconds since the UNIX epoch which happens to be 1970-01-01. There really is no reason to use the ODBC canonical form {d '1970-01-01'}.

DATEADD(ss, {unix timestamp}, '19700101')

Works just fine by the way.
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
If you don't want to change the language, but need to retrieve the date in a specific format then use:

CONVERT(CHAR(10), {your date column}, 101)

Where 101 is the US English format code for MM/DD/YYYY. As suggested by the syntax, you are converting to character data, so keep that in mind when using this technique. It should be done at the point of presentation/display. If any date (chronological) ordering or needs still exist for the date column, I would avoid converting it from a date if that makes sense.
0
 
shtaffaAuthor Commented:
Thank you gentlemen.

I ended up using the following query:
UPDATE <MyTable>
  SET <ConvertedDate = CONVERT(CHAR(10), DATEADD(second, <UnixTimeStamp>, {d '1970-01-01'}), 101)
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, your column is actually character data. For the reasons I said, this is not a best practice, but if consistent the conversion back is trivial. CONVERT(DATETIME, ConvertedDate, 101)

Anyway, best regards and happy coding,

Kevin
0
All Courses

From novice to tech pro — start learning today.