Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to convert Epoch date into human readable format


How to convert Epoch date into human readable format

Author
Message
n.heyen
n.heyen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 456
I need to import a flat file into a simple table. There are three 'epoch' date columns in the data. (Meaning they show the number of seconds since midnight 1-1-1970.) And look like this: 1352325814

I can use a SQL statement to convert them to UTC like this:
SELECT DATEADD(s, DATEDIFF(s, '1970-01-01 00:00:00', 1352325814), '1970-01-01 00:00:00') AS [UTC Date/Time]

to get something like 2012-11-07 22:03:00.000

I am trying to build a simple data flow with three tasks:
Flat File Source
Derived Column
OLE DB Destination
and the appropriate connections.

First, is this the way to do it? In the Derived Column task I have the expression setup like this:
"SELECT DATEADD( s, DATEDIFF(s, '1970-01-01 00:00:00', [dateTimeOrigination] ) , '1970-01-01 00:00:00' )"

where [dateTimeOrignination] is the epoch time I need to convert

When I run the package, it errors out after a few rows. The error messages are:

[Derived Column [2]] Error: The "Derived Column" failed because truncation occurred, and the truncation row disposition on "Derived Column.Inputs[Derived Column Input].Columns[dateTimeOrigination]" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (2) failed with error code 0xC020902A while processing input "Derived Column Input" (3). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[Flat File Source [17]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

When I open the CSV file in Excel, it looks right, I don't see any 'odd' data in the [dateTimeOrignination] column. Or anywhere else.

I know about enough SSIS to be dangerous but I think this looks like the way to load the data. Or is there another way?

And any ideas of where to start looking for what is causing these errors? Is there a problem with datatypes between the SSIS package and the database? The database file is set to datetime and the column property in the flat file connection is string[DT_STR] of 50.

The Test Connection works OK (everything is on my local desktop) and I'm running as SA.

Thanks for your time!
Norman
Jason-299789
Jason-299789
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1134 Visits: 3229
When I ran this in ssms


SELECT DATEADD(s, DATEDIFF(s, '1970-01-01 00:00:00', 1352325814), '1970-01-01 00:00:00')



I get an Arithmetic Overflow error reported.

And I cant for the life of me figure out why you need the datediff, which means the above can be simplifed to


Select DATEADD(s, 1352325814,'1970-01-01 00:00:00')



which when run returns the number you mentioned, at the top of your post.

In regards to the question what datatype is your original column as i believe it may need to be 8byte integer to hold these numbers, and the output should be a DT_DATE (i believe)

_________________________________________________________________________
SSC Guide to Posting and Best Practices
n.heyen
n.heyen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 456
Thank you Jason,

I had the 'opportunity' to be in meetings for about 7 hours yesterday and didn't see your reply until just before time to leave for the day...

But thank you for pointing out that I was using the wrong formula to convert. I guess if you have a lot of stuff you have been copy and paste, make sure you paste the right thing into the little box... Your formula is what I intended, not exactly sure where I used came from.;-)

In the Derived Column task I don't see a way to change the data type. Do I need to add a Data Conversion task as well?

And I'm beginning to think the data isn't DOS/ANSI format. I get what appears to be data type mismatch. I'm having someone see if they can convert it to DOS/ANSI or at least verify that isn't the problem.

Thanks for pointing out my error, and for assistance. I'll keep trying and see what happens. I'm not a heavy user, not this is heavy lifting, but I don't have any local help either.

Norman
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search