January 5, 2014 at 7:13 am
good day for everybody,
please advice regarding the below query, I've got an error
"Arthimatic overflowerror converting expression to data type datetime"
SELECT nEventLogIdn, DATEADD(s, nDateTime, '1970-01-01 00:00:00') AS nDateTime, nReaderIdn, nEventIdn, nUserID, nIsLog, nTNAEvent, nIsUseTA,
nType
FROM TB_EVENT_LOG
WHERE (DATEDIFF(Year, CONVERT(datetime, nDateTime, 120), GETDATE()) <= 0)
January 5, 2014 at 8:25 am
tamer.h (1/5/2014)
good day for everybody,please advice regarding the below query, I've got an error
"Arthimatic overflowerror converting expression to data type datetime"
SELECT nEventLogIdn, DATEADD(s, nDateTime, '1970-01-01 00:00:00') AS nDateTime, nReaderIdn, nEventIdn, nUserID, nIsLog, nTNAEvent, nIsUseTA,
nType
FROM TB_EVENT_LOG
WHERE (DATEDIFF(Year, CONVERT(datetime, nDateTime, 120), GETDATE()) <= 0)
Problem looks to be the CONVERT in your where clause. Based on the rest of the query the value in nDateTime appears to be seconds since 1970-01-01, not a datetime value.
January 6, 2014 at 1:28 am
yes, nDateTime=An elapsed time from Jan.1st 1970 00:00:00 to current time which shows seconds value.
and DATEADD(s, nDateTime, '1970-01-01 00:00:00') converting it to datetime format.
so, is there any way to correct it?
January 6, 2014 at 3:09 am
If you know that DATEADD(s, nDateTime, '1970-01-01 00:00:00') converts seconds since 1970-01-01 to a datetime,
why not simply swap the faulty expression in your WHERE clause for the the expression which works?
Whilst this would work, the expression on nDateTime would prevent SQL Server from using an index, and the
expression would have to be calculated for each row to determine a match to GETDATE() on the other side. The performance would suck.
Change the WHERE clause so that nDateTime is compared to the beginning and end of the current year,
expressed as seconds since 1970-01-01 00:00:00.
-- Calculate nDateTime for the beginning of the current year and next year
-- run this and inspect the results
SELECT
StartDate,
nStartDate = DATEDIFF(second,'1970-01-01 00:00:00',StartDate),
EndDate,
nEndDate = DATEDIFF(second,'1970-01-01 00:00:00',EndDate)
FROM (
SELECT
StartDate = DATEADD(year,DATEDIFF(year,0,GETDATE()),0),
EndDate = DATEADD(year,1+DATEDIFF(year,0,GETDATE()),0)
) d
-- Incorporate this into your query as follows:
-- set up 100 rows of sample data
;WITH TB_EVENT_LOG AS (
SELECT
nEventLogIdn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
nDateTime = ABS(CHECKSUM(NEWID()))%1400000000,
nReaderIdn = 3,
nEventIdn = 4,
nUserID = 5,
nIsLog = 1+ABS(CHECKSUM(NEWID()))%2,
nTNAEvent = 6,
nIsUseTA = 1+ABS(CHECKSUM(NEWID()))%2,
nType = 1+ABS(CHECKSUM(NEWID()))%3
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)
CROSS JOIN (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n)
)
-- and here's a modified query
SELECT
l.nEventLogIdn,
x.dtDateTime,
l.nReaderIdn,
l.nEventIdn,
l.nUserID,
l.nIsLog,
l.nTNAEvent,
l.nIsUseTA,
l.nType
FROM TB_EVENT_LOG l
CROSS APPLY (SELECT dtDateTime = DATEADD(s, nDateTime, '1970-01-01 00:00:00')) x
WHERE nDateTime >= DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,DATEDIFF(year,0,GETDATE()),0))
AND nDateTime < DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,1+DATEDIFF(year,0,GETDATE()),0))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 2:38 am
Thank you ChrisM@Work
it works perfectly.
I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but it always creating a new table in the same DB.....any idea?
January 8, 2014 at 2:42 am
tamer.h (1/8/2014)
Thank you ChrisM@Workit works perfectly.
Good, thanks for the feedback.
I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but it always creating a new table in the same DB.....any idea?
Without seeing the code you are using, I can only guess.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 4:38 am
tamer.h (1/8/2014)
Thank you ChrisM@Workit works perfectly.
I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but it always creating a new table in the same DB.....any idea?
Did you execute the code with the focus on the correct database? Use the three-part notation ({database}.{schema}.{object}) for each object in your code when you are working across multiple databases. Something like this simple example:
SELECT *
INTO [db_dest].[dbo].[result]
FROM [db_org].[dbo].[sample]
January 8, 2014 at 6:42 am
Hi All,
This is the modified code: and I've got the error "The multi-part identifier "originDB.dbo.neventidn" could not be bound."
SELECT
[originDB].[dbo].[nEventLogIdn],
[originDB].[dbo].[dtDateTime],
[originDB].[dbo].[nReaderIdn],
[originDB].[dbo].[nEventIdn],
[originDB].[dbo].[nUserID],
[originDB].[dbo].[nIsLog],
[originDB].[dbo].[nTNAEvent],
[originDB].[dbo].[nIsUseTA],
[originDB].[dbo].[nType]
INTO [db_dest].[dbo].[result]
FROM [originDB].[dbo].[TB_EVENT_LOG]
CROSS APPLY (SELECT dtDateTime=DATEADD(s, nDateTime, '1970-01-01 00:00:00')) x
WHERE nDateTime >= DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,DATEDIFF(year,0,GETDATE()),0))
AND nDateTime < DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,1+DATEDIFF(year,0,GETDATE()),0))
January 8, 2014 at 6:48 am
The whole of your output (everything in the SELECT list) is unrelated to anything else in your query. It looks like a bunch of columns but the syntax is incorrect. What are you trying to do? Revert back to where it was working and tell us what you want to do from there.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 8:33 am
Define the columns in the SELECT part only by their name (not using the three part notation). When a column-name exists in multiple objects (tables, sub-query) of the FROM part you'll need to add the table- or alias-name before the column name.
January 8, 2014 at 9:32 am
tamer.h (1/8/2014)
Thank you ChrisM@Workit works perfectly.
I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but it always creating a new table in the same DB.....any idea?
You are saying INSERT INTO but your code shows a SELECT...INTO.
If the table already exists, you cannot use SELECT INTO -- it creates a table every time. Therefore if you want to append rows to an existing table you must use a statement of the form:
INSERT INTO newdb.dbo.RESULT([field names])
SELECT [field names]
FROM olddb.dbo.[SOURCE TABLE]
WHERE...
If you want a new table name for each run, you must DROP the result table, or choose an unused table name.
Thanks
John.
January 9, 2014 at 12:28 am
Good Day for All,
and thank you so much for your advices.
I was very confused yesterday but finaly I've modified the code using SELECT INTO as below and it works perfectly
SELECT
nEventLogIdn,
dtDateTime,
nReaderIdn,
nEventIdn,
nUserID,
nIsLog,
nTNAEvent,
nIsUseTA,
nType
INTO db_dest.dbo.result
FROM TB_EVENT_LOG
CROSS APPLY (SELECT dtDateTime=DATEADD(s, nDateTime, '1970-01-01 00:00:00')) x
WHERE nDateTime >= DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,DATEDIFF(year,0,GETDATE()),0))
AND nDateTime < DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,1+DATEDIFF(year,0,GETDATE()),0))and neventidn=47
then I've used INSERT INTO to avoid creating the table each time but I've got the error "An explicit value for the identity column in table 'db_dest.dbo.result' can only be specified when a column list is used and IDENTITY_INSERT is ON."
also, i've used SET IDENTITY_INSERT result ON and got the same error.
TRUNCATE TABLE result
INSERT INTO db_dest.dbo.result
SELECT
nEventLogIdn,
dtDateTime,
nReaderIdn,
nEventIdn,
nUserID,
nIsLog,
nTNAEvent,
nIsUseTA,
nType
FROM TB_EVENT_LOG
CROSS APPLY (SELECT dtDateTime=DATEADD(s, nDateTime, '1970-01-01 00:00:00')) x
WHERE nDateTime >= DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,DATEDIFF(year,0,GETDATE()),0))
AND nDateTime < DATEDIFF(second,'1970-01-01 00:00:00', DATEADD(year,1+DATEDIFF(year,0,GETDATE()),0))and neventidn=47
January 9, 2014 at 8:28 am
Can you post the DDL statement you used to create the table [resut]?
Btw: your TRUNCATE statement is executed on the same database as your SELECT statement, because you didn't use a three-part notation. It is NOT executed against the table in your INSERT INTO statement.
January 9, 2014 at 2:34 pm
Hi HanShi,
I've created result table using the above SELECT INTO statement.
January 9, 2014 at 2:39 pm
If you use SELECT INTO to create the table, and then INSERT to populate it, the SELECT INTO will copy the identity specification. SELECT INTO is not really something I'd use in regular production, but that is simply my preference.
Why not just use a script to CREATE the table in the destination database one time, and then simply insert into it for all subsequent runs? That way, you can eliminate the identity specification from the destination table?
Thanks
John.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply