Conversion failed when converting date and/or time from character string - dynamic SQL and openquery
0
Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different amounts of quotes, conversions, etc. I believe it is due to the ImportDateTime column but Im not sure. Heavily edited the stored procedure for conciseness.
DECLARE
@SQL NVARCHAR(MAX)
,@SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@ErrorCode INT
,@checkcount BIGINT
,@rowcounter BIGINT
,@maxrowcount BIGINT
,@minTimeStamp DATETIME2
,@DateCollected DATETIME2
,@ImportDateTime DATETIME
,@AssessorDeploymentTimestamp DATETIME2
,@OrderSystemDeploymentTimestamp DATETIME2
SELECT
@checkcount = 0
,@rowcounter = 1
,@ImportDateTime = GETDATE();
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + @ImportDateTime + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND TimeStamp > ''' + @AssessorDeploymentTimestamp + ''' AND TimeStamp > ''' + @DateCollected + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + @DateCollected + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
) a
)'
Here is the error message output -
Msg 241, Sev 16, State 1, Line 197 : Conversion failed when converting date and/or time from character string. [SQLSTATE 22007]
Msg 0, Sev 16, State 1, Line 94 : SELECT MAX(CONVERT(DATETIME2,TimeStamp)) , NULL , NULL FROM AssessorLogDW.dbo.[Log] WHERE ServerSource = 'AOR-AOSQL01' AND DatabaseSource = '2018Q402' ; [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 111 : 2018-11-08 00:48:12.8830000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 136 : SELECT min(CONVERT(DATE,TimeStamp)),count(a.id),count(a.id) FROM (SELECT id, TimeStamp = CONVERT(DATE,TimeStamp) FROM openquery([AOR-AOSQL01], 'select Id, TimeStamp = CONVERT(DATE,TimeStamp) FROM [AOR-AOSQL01].[AssessorLog2018Q402].[dbo].[Log] WITH (NOLOCK) WHERE CONVERT(DATE,TimeStamp) > ''2018-11-04 00:48:12.8830000''') ) a [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 167 : @checkcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 168 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 169 : @maxrowcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 170 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 171 : @minTimeStamp [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 172 : 2018-11-05 00:00:00.0000000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 2018-10-26 15:05:43.5400000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 179 : 2018-10-26 14:37:43.8300000 [SQLSTATE 01000]
Any help is greatly appreciated.
tsql stored-procedures sql-server-2014 openquery
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
add a comment |
0
Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different amounts of quotes, conversions, etc. I believe it is due to the ImportDateTime column but Im not sure. Heavily edited the stored procedure for conciseness.
DECLARE
@SQL NVARCHAR(MAX)
,@SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@ErrorCode INT
,@checkcount BIGINT
,@rowcounter BIGINT
,@maxrowcount BIGINT
,@minTimeStamp DATETIME2
,@DateCollected DATETIME2
,@ImportDateTime DATETIME
,@AssessorDeploymentTimestamp DATETIME2
,@OrderSystemDeploymentTimestamp DATETIME2
SELECT
@checkcount = 0
,@rowcounter = 1
,@ImportDateTime = GETDATE();
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + @ImportDateTime + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND TimeStamp > ''' + @AssessorDeploymentTimestamp + ''' AND TimeStamp > ''' + @DateCollected + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + @DateCollected + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
) a
)'
Here is the error message output -
Msg 241, Sev 16, State 1, Line 197 : Conversion failed when converting date and/or time from character string. [SQLSTATE 22007]
Msg 0, Sev 16, State 1, Line 94 : SELECT MAX(CONVERT(DATETIME2,TimeStamp)) , NULL , NULL FROM AssessorLogDW.dbo.[Log] WHERE ServerSource = 'AOR-AOSQL01' AND DatabaseSource = '2018Q402' ; [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 111 : 2018-11-08 00:48:12.8830000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 136 : SELECT min(CONVERT(DATE,TimeStamp)),count(a.id),count(a.id) FROM (SELECT id, TimeStamp = CONVERT(DATE,TimeStamp) FROM openquery([AOR-AOSQL01], 'select Id, TimeStamp = CONVERT(DATE,TimeStamp) FROM [AOR-AOSQL01].[AssessorLog2018Q402].[dbo].[Log] WITH (NOLOCK) WHERE CONVERT(DATE,TimeStamp) > ''2018-11-04 00:48:12.8830000''') ) a [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 167 : @checkcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 168 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 169 : @maxrowcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 170 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 171 : @minTimeStamp [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 172 : 2018-11-05 00:00:00.0000000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 2018-10-26 15:05:43.5400000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 179 : 2018-10-26 14:37:43.8300000 [SQLSTATE 01000]
Any help is greatly appreciated.
tsql stored-procedures sql-server-2014 openquery
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19
add a comment |
0
0
0
Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different amounts of quotes, conversions, etc. I believe it is due to the ImportDateTime column but Im not sure. Heavily edited the stored procedure for conciseness.
DECLARE
@SQL NVARCHAR(MAX)
,@SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@ErrorCode INT
,@checkcount BIGINT
,@rowcounter BIGINT
,@maxrowcount BIGINT
,@minTimeStamp DATETIME2
,@DateCollected DATETIME2
,@ImportDateTime DATETIME
,@AssessorDeploymentTimestamp DATETIME2
,@OrderSystemDeploymentTimestamp DATETIME2
SELECT
@checkcount = 0
,@rowcounter = 1
,@ImportDateTime = GETDATE();
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + @ImportDateTime + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND TimeStamp > ''' + @AssessorDeploymentTimestamp + ''' AND TimeStamp > ''' + @DateCollected + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + @DateCollected + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
) a
)'
Here is the error message output -
Msg 241, Sev 16, State 1, Line 197 : Conversion failed when converting date and/or time from character string. [SQLSTATE 22007]
Msg 0, Sev 16, State 1, Line 94 : SELECT MAX(CONVERT(DATETIME2,TimeStamp)) , NULL , NULL FROM AssessorLogDW.dbo.[Log] WHERE ServerSource = 'AOR-AOSQL01' AND DatabaseSource = '2018Q402' ; [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 111 : 2018-11-08 00:48:12.8830000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 136 : SELECT min(CONVERT(DATE,TimeStamp)),count(a.id),count(a.id) FROM (SELECT id, TimeStamp = CONVERT(DATE,TimeStamp) FROM openquery([AOR-AOSQL01], 'select Id, TimeStamp = CONVERT(DATE,TimeStamp) FROM [AOR-AOSQL01].[AssessorLog2018Q402].[dbo].[Log] WITH (NOLOCK) WHERE CONVERT(DATE,TimeStamp) > ''2018-11-04 00:48:12.8830000''') ) a [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 167 : @checkcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 168 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 169 : @maxrowcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 170 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 171 : @minTimeStamp [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 172 : 2018-11-05 00:00:00.0000000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 2018-10-26 15:05:43.5400000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 179 : 2018-10-26 14:37:43.8300000 [SQLSTATE 01000]
Any help is greatly appreciated.
tsql stored-procedures sql-server-2014 openquery
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
Code below is our stored procedure that is trying to populate log data into our data warehouse. For the life of me I can't figure out why I'm getting this conversion error. All timestamp columns are datetime. I've tried a myriad of different amounts of quotes, conversions, etc. I believe it is due to the ImportDateTime column but Im not sure. Heavily edited the stored procedure for conciseness.
DECLARE
@SQL NVARCHAR(MAX)
,@SQL1 NVARCHAR(MAX)
,@SQL2 NVARCHAR(MAX)
,@ErrorCode INT
,@checkcount BIGINT
,@rowcounter BIGINT
,@maxrowcount BIGINT
,@minTimeStamp DATETIME2
,@DateCollected DATETIME2
,@ImportDateTime DATETIME
,@AssessorDeploymentTimestamp DATETIME2
,@OrderSystemDeploymentTimestamp DATETIME2
SELECT
@checkcount = 0
,@rowcounter = 1
,@ImportDateTime = GETDATE();
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + @ImportDateTime + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND TimeStamp > ''' + @AssessorDeploymentTimestamp + ''' AND TimeStamp > ''' + @DateCollected + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + @DateCollected + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
) a
)'
Here is the error message output -
Msg 241, Sev 16, State 1, Line 197 : Conversion failed when converting date and/or time from character string. [SQLSTATE 22007]
Msg 0, Sev 16, State 1, Line 94 : SELECT MAX(CONVERT(DATETIME2,TimeStamp)) , NULL , NULL FROM AssessorLogDW.dbo.[Log] WHERE ServerSource = 'AOR-AOSQL01' AND DatabaseSource = '2018Q402' ; [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 111 : 2018-11-08 00:48:12.8830000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 136 : SELECT min(CONVERT(DATE,TimeStamp)),count(a.id),count(a.id) FROM (SELECT id, TimeStamp = CONVERT(DATE,TimeStamp) FROM openquery([AOR-AOSQL01], 'select Id, TimeStamp = CONVERT(DATE,TimeStamp) FROM [AOR-AOSQL01].[AssessorLog2018Q402].[dbo].[Log] WITH (NOLOCK) WHERE CONVERT(DATE,TimeStamp) > ''2018-11-04 00:48:12.8830000''') ) a [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 167 : @checkcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 168 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 169 : @maxrowcount [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 170 : 1161246 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 171 : @minTimeStamp [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 172 : 2018-11-05 00:00:00.0000000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 178 : 2018-10-26 15:05:43.5400000 [SQLSTATE 01000]
Msg 0, Sev 16, State 1, Line 179 : 2018-10-26 14:37:43.8300000 [SQLSTATE 01000]
Any help is greatly appreciated.
tsql stored-procedures sql-server-2014 openquery
tsql stored-procedures sql-server-2014 openquery
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
edited Nov 13 '18 at 22:27
Jeff Lohr
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
asked Nov 13 '18 at 18:42
Jeff LohrJeff Lohr
336
336
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19
add a comment |
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19
add a comment |
1 Answer
1
active
oldest
votes
0
This has resolved my problem. The ImportDateTime needed to be converted as well as my Timestamp's and @DateCollected
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + CONVERT(VARCHAR(50),@ImportDateTime) + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
'
SELECT @SQL2 = N'
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND CONVERT(DATETIME2, TimeStamp) > ''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''' AND CONVERT(DATETIME2,TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287583%2fconversion-failed-when-converting-date-and-or-time-from-character-string-dynam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
This has resolved my problem. The ImportDateTime needed to be converted as well as my Timestamp's and @DateCollected
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + CONVERT(VARCHAR(50),@ImportDateTime) + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
'
SELECT @SQL2 = N'
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND CONVERT(DATETIME2, TimeStamp) > ''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''' AND CONVERT(DATETIME2,TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
add a comment |
0
This has resolved my problem. The ImportDateTime needed to be converted as well as my Timestamp's and @DateCollected
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + CONVERT(VARCHAR(50),@ImportDateTime) + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
'
SELECT @SQL2 = N'
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND CONVERT(DATETIME2, TimeStamp) > ''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''' AND CONVERT(DATETIME2,TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
add a comment |
0
0
0
This has resolved my problem. The ImportDateTime needed to be converted as well as my Timestamp's and @DateCollected
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + CONVERT(VARCHAR(50),@ImportDateTime) + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
'
SELECT @SQL2 = N'
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND CONVERT(DATETIME2, TimeStamp) > ''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''' AND CONVERT(DATETIME2,TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
This has resolved my problem. The ImportDateTime needed to be converted as well as my Timestamp's and @DateCollected
SELECT @SQL = N'
INSERT INTO ' + @ToDatabase + '.dbo.Log
(
ImportDateTime
,ServerSource
,DatabaseSource
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
)
(
SELECT
''' + CONVERT(VARCHAR(50),@ImportDateTime) + '''
,''' + @ServerSource + '''
,''' + @DatabaseSource + '''
,Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM (
select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters]
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
'
SELECT @SQL2 = N'
from openquery([' + @ServerSource + '],
''select
Id
,[Type]
,UserName
,AppCode
,SubscriptionCode
,Duration
,ServiceNamespace
,ServiceName
,MethodName
,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
,[Message]
,StackTrace
,Url
,UrlReferrer
,Browser
,BrowserVersion
,Platform
,[Timestamp]
,IpAddress
,EriAccountId
FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
WHERE URL LIKE ''http://online%'' AND CONVERT(DATETIME2, TimeStamp) > ''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''' AND CONVERT(DATETIME2,TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + ''' '') o
WHERE NOT EXISTS
(SELECT 1
FROM ' + @ToDatabase + '.dbo.Log b
WHERE b.id = o.id
AND CONVERT(DATETIME2, b.TimeStamp) > ''' + CONVERT(NVARCHAR(50),@DateCollected) + '''
AND b.ServerSource = ''' + @ServerSource + '''
)
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
answered Nov 13 '18 at 23:53
Jeff LohrJeff Lohr
336
336
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53287583%2fconversion-failed-when-converting-date-and-or-time-from-character-string-dynam%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Without having time to look through your whole code (maybe get it down to 2 or 3 lines that show the exact problem without the surrounding stuff), it depends on your language settings. What always works is 'yyyy-mm-ddThh:mm:ss(.sss)'
– ExternalUse
Nov 13 '18 at 19:19