logparser TO_TIME output not matching the sql time datatype

Question

Following is the QUERY from logparser :

logparser -i:Textline "SELECT TO_DATE(TO_TIMESTAMP(TRIM(EXTRACT_SUFFIX(text,0,'TIMESTAMP')),'M/d/yyyy'))AS DATE, TO_TIME(TO_TIMESTAMP(TRIM(EXTRACT_TOKEN(text,0,'(')),'H:mm:ss')) as TIME from 'C:\xxxx.log' where text like '%OUT:%' OR text like '%IN:%' OR text like '%TIMESTAMP%' OR text like '%DENIED%'" -o:datagrid

Output of the query is :

DATE           TIME
NULL           03:09:49
NULL           03:09:49
NULL           03:10:44
NULL           03:11:52
2015-02-06     03:12:40
NULL           03:13:37
NULL           03:18:22
NULL           03:21:18
NULL           03:21:32
NULL           03:24:19
NULL           03:26:02

When i want this data to flow into SQL table I get the following error:

QUERY :

logparser -i:Textline "select TO_DATE(TO_TIMESTAMP(TRIM(EXTRACT_SUFFIX(text,0,'TIMESTAMP')),'M/d/yyyy'))AS DATE, TO_TIME(TO_TIMESTAMP(TRIM(EXTRACT_TOKEN(text,0,'(')),'H:mm:ss')) as TIME INTO Ptclog from 'C:\ptclmgrd.log' where text like '%OUT:%' OR text like '%IN:%' OR text like '%TIMESTAMP%' OR text like '%DENIED%'" -o:SQL -server:KANNA\SQLEXPRESS -database:Flex -driver:"SQL Server Native Client 11.0" -username:sa -password:XXXXXX

ERROR:

Task aborted. SQL table column "Time" data type is not compatible with SELECT clause item "TIME" (type TIMESTAMP)

The data type selected in SQLEXPRESS is

time(0).

The data flows to the column if i select the datatype as :

"datetime"

But this adds a default date in front of time which is nuisance. e.g the time is shown as :

DATE        TIME
2015-02-06  2015-01-01 03:12:40.000
NULL        2015-01-01 07:55:49.000
NULL        2015-01-01 06:46:50.000
NULL        2015-01-01 06:55:40.000
NULL        2015-01-01 01:57:25.000
NULL        2015-01-01 02:36:41.000

Is there a way the time can be represented in hh:mm:ss format in sql express 2014 DB ?

Following are sample entries in the log file that is being queried:

3:12:40 (lmgrd) TIMESTAMP 2/6/2015
3:13:37 (ptc_d) OUT: "PROE_Flex3CG" e1cx@D43F2STC  
3:18:22 (ptc_d) OUT: "PROE_Flex3C" u234566@D5712Sf8  
3:21:18 (ptc_d) OUT: "PROE_Flex3C" u234566@D5712Sf8  
3:21:32 (ptc_d) OUT: "PROE_Flex3C" U524863@D9NR972S  
3:24:19 (ptc_d) IN: "PROE_Flex3CG" e1cx@D43F2STC  
3:26:02 (ptc_d) OUT: "PROE_Flex3C" m0ax@2SD3TC3F  
3:26:46 (ptc_d) OUT: "PROE_Flex3C" 06u1015@03tjn-proe  
3:26:48 (ptc_d) OUT: "26" 06u1015@03tjn-proe  
3:26:48 (ptc_d) OUT: "10114" 06u1015@03tjn-proe  
4:34:39 (ptc_d) DENIED: "26" 74U1027@MZBOXSGH61108  (Licensed number of users already reached. (-4,342:10054 ""))

score 0 · Accepted Answer · answered Mar 21 '15 at 08:43

0

Unfortunately no, LogParser does not support a time-of-day type. You may try to send your DATE and TIME columns together as a single Datetime, or send the TIME column as a string and hope SQL server will convert it to a Time...

answered Mar 21 '15 at 08:43

Gabriele Giuseppini

1,541
11
19

Thanks Gabriele. I had thought To_TIME would do the trick. Its really challenging to append Date Field in front of the pure time-stamp. In this case Date comes in some weird format that too at an interval of 6 hours. But then “A challenge only becomes an obstacle when you bow to it.” ! – Vaibhav Mhala Mar 22 '15 at 11:46

logparser TO_TIME output not matching the sql time datatype

1 Answers1