Problem with dates comparison, I need help please!RSS

5 replies

Last post Feb 26, 2007 04:56 PM by Anonymous

  • Problem with dates comparison, I need help please!

    Feb 21, 2007 03:38 AM|Anonymous|LINK

    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!

    W3C input format

  • Re: Problem with dates comparison, I need help please!

    Feb 21, 2007 06:47 AM|Anonymous|LINK

    Try combining Date and Time into a full timestamp the way the help file shows then do your comparison against that.

    W3C input format

  • Re: Problem with dates comparison, I need help please!

    Feb 21, 2007 08:17 AM|Anonymous|LINK

    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!

    W3C input format

  • Re: Problem with dates comparison, I need help please!

    Feb 22, 2007 11:43 AM|Anonymous|LINK

    this should work:

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

     

     

    W3C input format

  • Re: Problem with dates comparison, I need help please!

    Feb 24, 2007 04:59 PM|Anonymous|LINK

    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

    W3C input format

  • Re: Problem with dates comparison, I need help please!

    Feb 26, 2007 04:56 PM|Anonymous|LINK

    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!

    W3C input format