SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert: only data that hasn't been inserted prior!
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

mitin
Yak Posting Veteran

81 Posts

Posted - 06/13/2013 :  04:06:59  Show Profile  Reply with Quote
sorry what do you mean exactly?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/13/2013 :  04:13:30  Show Profile  Reply with Quote
quote:
Originally posted by mitin

sorry what do you mean exactly?


I meant the format in which you've date values. The current error may be result of not having the date values stored in [Timestamp] in proper format

see this for more details

http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/13/2013 :  04:48:43  Show Profile  Reply with Quote
ah ok, it is a strange formate that the timestamp is in, and this is dictated by the application that actually inserts the data into the k99.genalarmpushalarm table.
It is called 'candletimestamp' format, and as I have said, it is inserted as a Char value into the k99.genalarmpushalarm table, here is an example of one of these timestamps:

1130604081815000


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/13/2013 :  05:02:59  Show Profile  Reply with Quote
so what does that value mean? how can you identify what date value it represents?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/13/2013 :  05:09:27  Show Profile  Reply with Quote
1130604081815000

this is kind of a guess but:

04/06/13 08:18:15 000

its something close to that...

is this definately the cause of my latest error?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/13/2013 :  05:17:06  Show Profile  Reply with Quote
quote:
Originally posted by mitin

1130604081815000

this is kind of a guess but:

04/06/13 08:18:15 000

its something close to that...

is this definately the cause of my latest error?


Ok...Are values are existing in both the tables (k99.genalarmpushalarm and logicaldisksample) in same format? if yes, then i guess it shouldnt cause current error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/13/2013 :  07:15:16  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by mitin

1130604081815000

this is kind of a guess but:

04/06/13 08:18:15 000

its something close to that...

is this definately the cause of my latest error?


Ok...Are values are existing in both the tables (k99.genalarmpushalarm and logicaldisksample) in same format? if yes, then i guess it shouldnt cause current error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




I think its becuase of the

DECLARE @MaxTimeStamp datetime
SELECT @MaxTimeStamp= max(timestamp) from logicaldisksample --implicit convertion of a char/decimal value into datetime


I believe replacing it with "Varchar(20)" datatype will solve the problem. Though Mitin has mentioned that datatype for a column in one table is decimal and for the other is char but SQL will implicitly convert it unless there is no problematic data
quote:

FYI in the K99.genalarmpushalarm table the datatype for timestamp is 'char'
whereas in the logicaldisk sampl table it is 'decimal'



@mitin, I would suggest to use standard datatypes for date inforamtion e.g. Date or DateTime as required, instead of using char/decimal which usually cause such issues.

Cheers
MIK
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/13/2013 :  11:34:23  Show Profile  Reply with Quote
hi, I have tried changing the data type in the logicaldisktable from decimal to datetime however I still get the error that:

conversion failed when converting date and/or time from character string

I realise that this is because the data type of the column that the timestamp is stored in in the k99genalarmpushalarm table is 'char', however as stated, this must be char due to the application that inserts the data.

How can I get around this issue, is there any sql code that I need to include that will mean that the char data can be cast from char to datetime or decimal?

Thanks
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/13/2013 :  11:49:01  Show Profile  Reply with Quote
Do not change the field's datatype as it won't work in your case, since the data in the table is not in DATE format.

as I mentioned earlier, change the datatype of the @MaxTimeStamp variable from DateTime to Varchar(20) should hopefully will resolve this issue

Cheers
MIK
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/14/2013 :  05:15:50  Show Profile  Reply with Quote
ok thanks this query now runs! however there are no results shown for it, the select returns 0 rows.

i thought that this would return rows not sure whats going on, need to use this in my insert statement
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  05:29:53  Show Profile  Reply with Quote
quote:
Originally posted by mitin

ok thanks this query now runs! however there are no results shown for it, the select returns 0 rows.

i thought that this would return rows not sure whats going on, need to use this in my insert statement


that may because the value it stores will not in same sequence as date equivalents

try converting them first to date equivalents and then do the > comparison

you can use a logic like below example


DECLARE @Dateval char(16) = '1130604081815000'

SELECT CAST(CAST(LEFT(@Dateval,LEN(@Dateval)-13)+1900 AS char(4)) + LEFT(RIGHT(@dateval,13),4) + ' ' + STUFF(STUFF(STUFF(RIGHT(@Dateval,9),3,0,':'),6,0,':'),9,0,'.') as datetime)




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/14/2013 :  05:53:12  Show Profile  Reply with Quote
wow, im really sorry but very im really not sure how your code above works, or where it would fit into my query atm, im very short for time and do not have the time to research into this atm, i hope you don't mind this. my code as stand is below, could you give me an example of how the code you are suggesting would fit into my query?

thanks so much, very urgent!!




DECLARE @MaxTimestamp varchar(20)

SELECT @MaxTimestamp= max(timestamp) 
from logicaldisksample

insert into logicaldisksample
(
PMHost,
PMInstance,
Timestamp,
PMObject,
[DiskWriteBytesSec],
[PercFreeSpace],
[FreeMegabytes],
[SplitIOSec],
[MDLReadsSec],
[Threads],
[InterruptsSec],
[PacketsReceivedNonUnicastSec],
[PercPinReadHits],
[TransitionFaultsSec]

)


SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM 
(SELECT  PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER 
FROM [Warehouse].[sa_itm_user].[K99_GENALARMPUSHALARM]
where timestamp > @MaxTimestamp
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in 
	(
	[Disk Write Bytes/Sec],
	[% Free Space],
	[Free Megabytes],
	[Split IO/Sec],
	[MDL Reads/sec],
	[Threads],
	[Interrupts/sec],
	[Packets Received Non-Unicast/sec],
	[Pin Read Hits %],
	[Transition Faults/sec]
        )
     )P 
	
where PMObject = 'logicaldisk' and
(
 [Disk Write Bytes/Sec] is not null or
 [% Free Space]is not null or
 [Free Megabytes] is not null or
 [Split IO/Sec] is not null or
 [MDL Reads/sec] is not null or
 [Threads] is not null or
 [Interrupts/sec] is not null or
 [Packets Received Non-Unicast/sec] is not null or
 [Pin Read Hits %] is not null or
 [Transition Faults/sec] is not null
) 
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  06:05:30  Show Profile  Reply with Quote
Replace columns in
timestamp > @MaxTimestamp
with the corresponding expressions

CAST(CAST(LEFT([timestamp],LEN([timestamp])-13)+1900 AS char(4)) + LEFT(RIGHT([timestamp],13),4) + ' ' + STUFF(STUFF(STUFF(RIGHT([timestamp],9),3,0,':'),6,0,':'),9,0,'.') as datetime) >
CAST(CAST(LEFT(@MaxTimestamp,LEN(@MaxTimestamp)-13)+1900 AS char(4)) + LEFT(RIGHT(@MaxTimestamp,13),4) + ' ' + STUFF(STUFF(STUFF(RIGHT(@MaxTimestamp,9),3,0,':'),6,0,':'),9,0,'.') as datetime)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/14/2013 :  06:11:08  Show Profile  Reply with Quote
sorry but to be absolutely clear, literally replace this part of the query:

timestamp > @MaxTimestamp

with the code you have posted above? just confused by why you say change 'columns'?

Edited by - mitin on 06/14/2013 06:11:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/14/2013 :  06:41:49  Show Profile  Reply with Quote
yep...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/14/2013 :  06:53:32  Show Profile  Reply with Quote
@mitin: Now, don't say that TimeStamp is not a Column :)

Try this code
DECLARE @MaxTimestamp datetime
SELECT @MaxTimestamp= max(timestamp)
Max(convert(datetime,Convert(varchar,convert(date,SUBSTRING([timestamp],2,6))) +' '+ Convert(varchar,STUFF(STUFF(STUFF(RIGHT([timestamp],9),3,0,':'),6,0,':'),9,0,'.')))) --This is converting the char/decimal value into date format. It will work only if the TimeStamp data in the table is EXACTLY same as that of sample data and the explanation you provided as how its interpreted as date
from logicaldisksample

insert into logicaldisksample
(
PMHost,
PMInstance,
Timestamp,
PMObject,
[DiskWriteBytesSec],
[PercFreeSpace],
[FreeMegabytes],
[SplitIOSec],
[MDLReadsSec],
[Threads],
[InterruptsSec],
[PacketsReceivedNonUnicastSec],
[PercPinReadHits],
[TransitionFaultsSec]

)
SELECT
PMHost,
PMInstance,
Timestamp,
PMObject,
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
FROM
(SELECT PMHost, PMInstance, Timestamp, PMObject,PMValue, PM_COUNTER
FROM [Warehouse].[sa_itm_user].[K99_GENALARMPUSHALARM]
where timestamp
convert(datetime,Convert(varchar,convert(date,SUBSTRING([timestamp],2,6))) +' '+ Convert(varchar,STUFF(STUFF(STUFF(RIGHT([timestamp],9),3,0,':'),6,0,':'),9,0,'.'))) > @MaxTimestamp
) T1
PIVOT(MAX(PMValue) FOR PM_Counter in
(
[Disk Write Bytes/Sec],
[% Free Space],
[Free Megabytes],
[Split IO/Sec],
[MDL Reads/sec],
[Threads],
[Interrupts/sec],
[Packets Received Non-Unicast/sec],
[Pin Read Hits %],
[Transition Faults/sec]
)
)P

where PMObject = 'logicaldisk' and
(
[Disk Write Bytes/Sec] is not null or
[% Free Space]is not null or
[Free Megabytes] is not null or
[Split IO/Sec] is not null or
[MDL Reads/sec] is not null or
[Threads] is not null or
[Interrupts/sec] is not null or
[Packets Received Non-Unicast/sec] is not null or
[Pin Read Hits %] is not null or
[Transition Faults/sec] is not null
)

Still if it does not give you any result them "make sure" that data exists for the date greater than the @MaxTimeStamp and per imposed conditions e.g.
where PMObject = 'logicaldisk' and
(
[Disk Write Bytes/Sec] is not null or
[% Free Space]is not null or
[Free Megabytes] is not null or
[Split IO/Sec] is not null or
[MDL Reads/sec] is not null or
[Threads] is not null or
[Interrupts/sec] is not null or
[Packets Received Non-Unicast/sec] is not null or
[Pin Read Hits %] is not null or
[Transition Faults/sec] is not null
)

Cheers
MIK
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 06/14/2013 :  08:36:50  Show Profile  Reply with Quote
I now get 'argument data type decimal is invalid for argument 1 of the substring, remember that @maxtimstamp needds to be varchar, but is decimal in your query, is this the reason?
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 06/14/2013 :  09:23:14  Show Profile  Reply with Quote
Replace the above red marked section with below .. the change is in blue color

Max(convert(datetime,Convert(varchar,convert(date,SUBSTRING(Convert(varchar,[timestamp]),2,6))) +' '+ Convert(varchar,STUFF(STUFF(STUFF(RIGHT(Convert(varchar,[timestamp]),9),3,0,':'),6,0,':'),9,0,'.'))))

Cheers
MIK

Edited by - MIK_2008 on 06/14/2013 09:23:53
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000