Solved

MS SQL unix timestamp to mm/dd/yyyy conversion

Posted on 2011-09-20
9
536 Views
Last Modified: 2012-06-27
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?
0
Comment
Question by:shtaffa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 36569080
Borrowing heavily from another source...

DATEADD(second, @timestamp, {d '1970-01-01}) where @timestamp is the value for your unix timestamp?
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 36569109
And as I test in sql 2005... it looks like there is a quote missing, so:
DATEADD(second, <unixtimestampvalue>, {d '1970-01-01'})
0
 

Author Comment

by:shtaffa
ID: 36569133
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 30

Accepted Solution

by:
Rich Weissler earned 350 total points
ID: 36569184
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
 

Author Comment

by:shtaffa
ID: 36569261
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36569307
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
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 150 total points
ID: 36569330
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
 

Author Closing Comment

by:shtaffa
ID: 36569520
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36569573
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL remove duplicates from different columns 14 52
SQL Percentage Formula 7 33
Moving away from Access 2003 adp files 4 47
SQL query 45 40
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 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