SQL Syntax for Log Parser Time Date functions - Exchange 2003 Tracking Logs

Hello - I am trying to use Log Parser 2.2 to review Exchange 2003 Tracking Logs.

I want to compare the Date and Time fields to the Origination-Time field, as you can see in the Date and Time fields are separate and the Origination-Time combines the date and time.

Can someone give me advice on how I would be able to compare the two values (Date, Time) and (Origination-Time), I am looking for the difference in seconds.


Example Data:
Date 2010-3-15
Time 0:0:19 GMT
Origination-Time 2010-3-14 23:55:49 GMT

LVL 15
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
This has nothing to do with MS SQL Server, for better response I suggest you get a Moderator to change that zone to SQL Query Syntax zone:

Having said that, please post your query as it stands now.
markpalinuxAuthor Commented:

This query got me the local time and removed the "gmt" , not sure how to get a diff of those time stamps via sql syntax, currently looking at powershell to read the csv and find the differences in seconds between the two times.

"c:\Program Files (x86)\Log Parser 2.2\LogParser.exe" -i:W3C -o:CSV "Select TO_LOCALTIME(TO_Timestamp(REPLACE_STR(STRCAT(STRCAT(date,' '), time),' GMT',''),'yyyy-M-d h:m:s') ) as DateTime,MSGID,TO_LOCALTIME(TO_TIMESTAMP(REPLACE_STR(Origination-Time,' GMT',''),'yyyy-M-d h:m:s')) as ForOrigTime INTO BigReport3.csv from C:\Temp\exchange_tracking\20100315.log WHERE Event-ID IN (1027;1028)"

2010-03-14 20:00:19,36119119-3jdsk-23jjj-sdad-23jksdjk3423@gmail.com,2010-03-14 19:55:49

Ted BouskillSenior Software DeveloperCommented:
This URL has all the LogParser functions with some samples that will suit your needs.

Specifically there is a function called QUANTIZE() that you can use to convert a timestamp to seconds to then use in your comparisons

Usually I use LogParser to push logs into a SQL database where I can then use a more feature rich syntax to process the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
markpalinuxAuthor Commented:

Thank you for the information.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.