2

Consider the following to outputs from the same query in ET:

select JobID, SubscriberKey, EventDate from _open with (nolock) where EventDate > '2013-04-30' and EventDate < '2013-10-31'

Outputs to a lot of lines of data, but I am curious about this:

31502339        2013-11-30 11:27:51     14234713@qq.com 
31502339        2013-11-30 11:27:51.020000000   14234713@qq.com

Why do we need that much precision

Both lines are output from the same field and on the surface appear to be the same value. Any way to normalize it to the same format?

  • Date/Time is stored as a floating point in T-SQL. Here is some documentation regarding T-SQL and Date/Time. – Kelly J Andrews Jan 7 '14 at 14:45
1

If you want to remove milliseconds from your query result, you can use a CONVERT function.

CONVERT(VARCHAR, eventdate, 20) AS EventDate

The full query:

SELECT jobid, subscriberkey, CONVERT(VARCHAR, eventdate, 20) AS EventDate FROM _open WITH (nolock) WHERE eventdate > '2013-04-30' AND eventdate < '2013-10-31'

I believe when there is no milliseconds present it is meant to be zero milliseconds or '.000000000'.

|improve this answer|||||
  • I don't understand why 95% of the entries would land on the "whole" second, and 5% would have the milliseconds, especially in the case where it is the exact same event type , same subscriberkey and virtually the same eventdate. – Paul Shriner Jan 7 '14 at 18:10
  • How accurate of a number is 95%? I just pulled 3600 records from a recent send job out of the send log and I am seeing 1.3% landing on the whole number. However, this is for sent time, not open event. Based on that number you can see where I made the assumption, but 95% does seem odd. – Jon Sakas Jan 8 '14 at 19:52
  • I did a sampling of the file. 500 rows were in the sample and there were 10 with milliseconds and 490 without. The file is quite large and it appears that that ratio scales as I read more lines in. – Paul Shriner Jan 8 '14 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.