« Previous Next »

Thread: Problem with dates comparison, I need help please!

Last post 02-26-2007 4:56 PM by LogParser User : juasebsto. 5 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (6 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 02-21-2007, 3:38 AM

    Problem with dates comparison, I need help please!

    Reply Contact

    Hi there:

    I'm having a problem with a query. I'm trying to tranfer data from a log file with W3C format to a MsSQL database, I'm doing it with Visual Basic. I need to get all the records after a specific date and time, for instance after the "10-02-2007 00:17:40". I wrote the query below, the log parser didn't show any error but there were no records as a result and I know there are records after that date in the log files.

    "SELECT Date, Time, client-ip, Client-hostname, Partner-Name, Server-hostname, server-IP, Recipient-Address, Event-ID, MSGID, Priority, Recipient-Report-Status, total-bytes, Number-Recipients, Origination-Time, Encryption, service-Version, Linked-MSGID, Message-Subject, Sender-Address

    FROM 'C:\logs\*.log'

    WHERE TO_TIMESTAMP(Date,'dd-MM-yyyy') > TO_TIMESTAMP('10-02-2007','dd-MM-yyyy')

    OR

    (TO_TIMESTAMP(Date,'dd-MM-yyyy') = TO_TIMESTAMP('10-02-2007','dd-MM-yyyy') AND TO_TIMESTAMP(Time,'hh:mm:ss') >= TO_TIMESTAMP('00:17:40','hh:mm:ss'))

    ORDER BY Date, Time"

    If someone can figure out what is wrong on the query, please let me know

    Thanks very much

    juan!
    Tags:

  • 02-21-2007, 6:47 AM In reply to

    RE: Problem with dates comparison, I need help please!

    Mark as Answer
    Reply Contact
    Try combining Date and Time into a full timestamp the way the help file shows then do your comparison against that.
    Tags:

  • 02-21-2007, 8:17 AM In reply to

    RE: Problem with dates comparison, I need help please!

    Mark as Answer
    Reply Contact
    LogParser User : juasebsto:
    Quote: Try combining Date and Time into a full timestamp the way the help file shows then do your comparison against that.

    Hi Daniel,

    Thanks for you answer. I really don't understand what is going on with this, it seems that Logparser ingnore the information. I even tried this simple query....

    WHERE TO_DATE(TO_TIMESTAMP(Date,'yyyy-MM-dd')) = TIMESTAMP('2007-02-10', 'yyyy-MM-dd')

    ... and logparser doesn't show any error but it returns 0 records and I know there are plenty of records on this date in the log file.

    Do you think there may be other way to do it?

    Thanks

    juan!
    Tags:

  • 02-22-2007, 11:43 AM In reply to

    RE: Problem with dates comparison, I need help please!

    Mark as Answer
    Reply Contact

    this should work:

    WHERE TO_TIMESTAMP(Date,'yyyy-MM-dd') = TO_TIMESTAMP('2007-02-10', 'yyyy-MM-dd')

     

     
    Tags:

  • 02-24-2007, 4:59 PM In reply to

    RE: Problem with dates comparison, I need help please!

    Mark as Answer
    Reply Contact

    Hey.. i dont tknow if this is what u need, but it works for me: 

     WHERE date >= TIMESTAMP( '2006-09-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' )
     AND date <= TIMESTAMP( '2006-10-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' )

    Cheers
    Tags:

  • 02-26-2007, 4:56 PM In reply to

    RE: Problem with dates comparison, I need help please!

    Mark as Answer
    Reply Contact
    LogParser User : juasebsto:
    Quote: Hey.. i dont tknow if this is what u need, but it works for me:   WHERE date >= TIMESTAMP( '2006-09-28 00:00:00', 'yyyy-MM-dd hh:mm:ss' )  AND date <= TIMESTAMP( '2006-10-28 00:0...

    Hi there,

    Thanks very much for your answers. You may be think I'm crazy but I resolved it with the following script:

    TO_TIMESTAMP(TO_STRING(STRCAT(STRCAT(STRCAT(STRCAT(SUBSTR(Date,0,4),'-'),REPLACE_CHR(SUBSTR(Date,5,2),'-','')),'-'),REPLACE_CHR(SUBSTR(Date,7,ADD(STRLEN(Date),-7)),'-',''))),'yyyy-M-d') > TIMESTAMP('" + DateLastItem + "','yyyy-M-d')

    OR

    ( (TO_TIMESTAMP(TO_STRING(STRCAT(STRCAT(STRCAT(STRCAT(SUBSTR(Date,0,4),'-'),REPLACE_CHR(SUBSTR(Date,5,2),'-','')),'-'),REPLACE_CHR(SUBSTR(Date,7,ADD(STRLEN(Date),-7)),'-',''))),'yyyy-M-d') = TIMESTAMP('" + DateLastItem + "','yyyy-M-d'))

    AND

    (TO_TIMESTAMP(REPLACE_STR(REPLACE_CHR(Time,' ',''),'GMT',''),'h:m:s') >= TIMESTAMP('" + TimeLastItem + "','h:m:s')) ))

    I tested it with many log files and it does what I need. I found out after a while that there are some little problems when dates come with the format yyyy-M-d even if you convert them with the CONVERT function to yyyy-MM-dd.

    I hope it can be useful for someone.

    juan!
    Tags:
Page 1 of 1 (6 items)
Microsoft Communities