Epoch to readable format

Question

Post reply

Epoch to readable format

prady_1988

Say Hey Kid

Points: 680
More actions
February 28, 2011 at 1:11 am

#246298

Hi all. I need to convert epoch to readable format.I want it in a readable/sql server datetime format.
In Oracle this was possibly as there was a helpful built-in function.
select to_char(timestamp '1970-01-01 00:00:00' + numtodsinterval(IntegerDate, 'SECOND'),'MM/DD/yyyy HH24:MI:SS') End_Date from dual;
numtodsinterval() was really helpful in conversion. IntegerDate being the epoch end date.
Is there any alternative function to numtodsinterval in sql server?
Or please let me know if there is any other way to solve this.
Thanks in advance.
Prady

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I think you are looking for something like?? If I understood your question correctly.

select DATEADD(ss, yourEpochDateIntegerHere, '1970/01/01') from YourTableWithEpochInteger

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

prady_1988 Say Hey Kid Points: 680 More actions · Answer 2

Hi Sean,

This is the result i was looking for but the actual required result is 5 hours behind GMT and daylight saving times are to be considered.

Would that be possible by modifying this somehow?

Thanks in advance,

Prady

Lowell SSC Guru Points: 323518 More actions · Answer 3

can you add the # hours difference when calculating the server's getdate to the utc date?

for example, this returns 5 form my EST server...it might vary depending on daylight savings:

'

--'returns 5

select datediff(hh,GETDATE(),GETUTCDATE())

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

something like this should get you pretty close.

select DATEADD(ss, 211114134, '1970/01/01'), DATEADD(hh, datediff(hh, GETUTCDATE(), GETDATE()), DATEADD(ss, 211114134, '1970/01/01'))

Lowell - you had the calculation backwards. You would want -5 for EST. I am CST which is currently -6 GMT. This will adjust correctly for daylight saving adjustments.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

prady_1988 Say Hey Kid Points: 680 More actions · Answer 5

Sorry for the late reply guys. was busy with something else.

Anyway thanks for helping me out.

But this is the problem.

It should include day light saving timings. Here is an example

1162101599 should be converted as Sunday, October 29, 2006 01:59:59

But one second later, when daylight savings swing back:

1162101600 should be Sunday, October 29, 2006 01:00:00 and not

October 29, 2006 02:00:00.000

Darryl Wilson Mr or Mrs. 500 Points: 526 More actions · Answer 6

I have a varchar field which was used to import the epoch value from a csv.

I need to update a datetime column to the proper date. BUT it wont let me put the epoch field in the Update statement

This is what I try and its result

UPDATE [SU] set CreatedTimeDT = DATEADD(ss, convert(varchar, CreatedTime) ,'19700101') WHERE ltrim(CREATEDTIME)=CAST(CreatedTime AS VARCHAR)

Msg 8116, Level 16, State 1, Line 1

Argument data type varchar is invalid for argument 2 of dateadd function.

I've written an SP that creates a string and executes, but there are Millions of rows in each table and it kills the server.

Any suggestions please.

Darryl Wilson
darrylw99@hotmail.com

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Darryl Wilson wrote:

I have a varchar field which was used to import the epoch value from a csv.
I need to update a datetime column to the proper date. BUT it wont let me put the epoch field in the Update statement
This is what I try and its result
UPDATE [SU] set CreatedTimeDT = DATEADD(ss, convert(varchar, CreatedTime) ,'19700101') WHERE ltrim(CREATEDTIME)=CAST(CreatedTime AS VARCHAR)
Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 2 of dateadd function.
I've written an SP that creates a string and executes, but there are Millions of rows in each table and it kills the server.
Any suggestions please.

For starters this should be a new question, not piggybacked onto a thread that is 9 years old. Secondly, the error message is quite clear, your second argument to DATEADD is a varchar because you explicity convert to that data type. But you also have a problem because you don't specify the length of your varchar. This is a bad approach as the length will use the default length. Did you know that the default changes depending on usage? In a convert it will be 30, for variables or parameters it will default to 1. Save yourself the anguish and be explicit. But your code has other bad smells as well. Things like LTRIM on a what appears to be a date or datetime. Comparing strings for equality instead of dates.

My suggestion would be to create a new question. In that question include your code AND DDL for the tables, sample data, and desired output.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/