HOWTO: Determining the day of the week from the date in Microsoft Logparser

One of the issues we encountered in working with Microsoft Logparser is that we needed to determine which day of the week it was given a date.  Though there wasn't any function to do so, it seemed that the solution was pretty simple, actually.

(Apologize in advance for the bad reading formatting here, but these should work just by copying and pasting into your command prompt.  For some additional sanity I colour the more important parts red, heh.)

The code itself to convert is like this:

MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7)

Where DateTime is the input column with the datetime stamp.  86400 is the number of seconds in a day, and 7 is well....the number of days in a week :P

The return values (proved consistent within the range of my testing) are with Sunday as 1, and running all the way to Saturday as 0, i.e.:

Sat - 0

Sun - 1

Mon - 2

Tue - 3

Wed - 4

Thu - 5

Fri - 6

Combined into a simple query:

logparser -i:csv -o:datagrid "SELECT DISTINCT TO_DATE(DateTime), MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) FROM datetest.csv"

(datetest.csv is my input test file here.)

And making use of some additional (simple but crazy!) programming to output as days instead of numbers (referencing from this article)

logparser -i:csv -o:datagrid "SELECT DISTINCT TO_DATE(DateTime), Day USING CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 0 THEN 'SAT' ELSE CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 1 THEN 'SUN' ELSE CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 2 THEN 'MON' ELSE CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 3 THEN 'TUE' ELSE CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 4 THEN 'WED' ELSE CASE MOD(DIV(TO_INT(TO_DATE(DateTime)), 86400), 7) WHEN 5 THEN 'THU' ELSE 'FRI' END END END END END END AS Day FROM datetest.csv"

The output that I get :D

Hope this helped you.  Any comments/feedback is much appreciated!

References:

• http://logparserplus.com/
  
• http://technet.microsoft.com/en-us/magazine/2006.08.insidemsft.aspx