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     [email protected] 
31502339        2013-11-30 11:27:51.020000000   [email protected]

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?

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

1 Answer 1

Reset to default
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
3
  • 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
    Commented Jan 7, 2014 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
    Commented Jan 8, 2014 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
    Commented Jan 8, 2014 at 20:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .