April 27, 2018 at 6:44 pm
Hi,
I am getting strange error 9810 "The datepart millisecond is not supported by date function dateadd for data type date.", seems to come from doing something like this:
UPDATE dbo.[myTable] SET
...
, [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...
OUTPUT inserted.[id] INTO @open_id
FROM
#temp t
JOIN...
WHERE ...
both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME, still gives me that error...
Happens at one particular server, at all others seems to work fine
should I mention it's never happened in this server before either? 🙂
Fluc?
April 28, 2018 at 12:00 am
btio_3000 - Friday, April 27, 2018 6:44 PMHi,
I am getting strange error 9810 "The datepart millisecond is not supported by date function dateadd for data type date.", seems to come from doing something like this:
UPDATE dbo.[myTable] SET
..., [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...OUTPUT inserted.[id] INTO @open_id
FROM#temp t
JOIN...WHERE ...
both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME, still gives me that error...
Happens at one particular server, at all others seems to work fine
should I mention it's never happened in this server before either? 🙂Fluc?
The DATE data type does not have a time element, it is simply a three byte storage for number of days since 0001-01-01, hence trying to alter the time element is a futile exercise.
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @TDATE DATE = '20180101';
SELECT @TDATE AS T_DATE
,CONVERT (INT,SUBSTRING(CONVERT(VARBINARY(3),@TDATE,0),3,1)
+SUBSTRING(CONVERT(VARBINARY(3),@TDATE,0),2,1)
+SUBSTRING(CONVERT(VARBINARY(3),@TDATE,0),1,1)) AS TD_NUMVAL
,DATEDIFF(DAY,'00010101',@TDATE) AS DD_FROM_ONE;
Output
T_DATE TD_NUMVAL DD_FROM_ONE
---------- ----------- -----------
2018-01-01 736694 736694
April 28, 2018 at 9:52 am
Thanks for replying,
Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...
Thanks again
April 28, 2018 at 2:07 pm
btio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
Based on the error you're getting, you might want to double check that.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2018 at 2:10 am
btio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.
April 29, 2018 at 11:05 am
Eirikur Eiriksson - Sunday, April 29, 2018 2:10 AMbtio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.
CREATE TABLE dbo.[myTable](
[id] BIGINT IDENTITY( 1, 1 ) NOT NULL
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED ( [id] ),
[Date2] DATETIME NULL,
...
)
CREATE TABLE #t(
[id] INT IDENTITY NOT NULL,
[date1] DATETIME NULL,...
)
then there is an INSERT into #t from a table where [date1] is being populated from varchar(50)
and finally
UPDATE
dbo.[myTable] SET
...
, [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...
OUTPUT inserted.[id] INTO @open_id
FROM
#temp t
JOIN...
WHERE ...
pretty straight forward... sorry, I do not have an execution plan from the server where/when it happened and I was not able to duplicate an error even at that same server while running "manually" against the same dataset...
all the data in varchar(50) is in
2011-01-03 00:00:00.000
format, i.e. all the time part is actually zeros... Is it possible that somehow server considers it as a date still while inserting into temp table even though temp table says datetime? I know how it sounds
Thank you
April 29, 2018 at 11:15 am
btio_3000 - Sunday, April 29, 2018 11:05 AMEirikur Eiriksson - Sunday, April 29, 2018 2:10 AMbtio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.CREATE TABLE dbo.[myTable](
[id] BIGINT IDENTITY( 1, 1 ) NOT NULL
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED ( [id] ),
[Date2] DATETIME NULL,
...
)
CREATE TABLE #t(
[id] INT IDENTITY NOT NULL,
[date1] DATETIME NULL,...
)
then there is an INSERT into #t from a table where [date1] is being populated from varchar(50)
and finally
UPDATE
dbo.[myTable] SET
..., [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...OUTPUT inserted.[id] INTO @open_id
FROM#temp t
JOIN...WHERE ...
pretty straight forward... sorry, I do not have an execution plan from the server where/when it happened and I was not able to duplicate an error even at that same server while running "manually" against the same dataset...
all the data in varchar(50) is in
2011-01-03 00:00:00.000
format, i.e. all the time part is actually zeros... Is it possible that somehow server considers it as a date still while inserting into temp table even though temp table says datetime? I know how it sounds
Thank you
Do you have the datetime optimization enabled for the server that's having the problem? If so, try turning it off and see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2018 at 12:04 pm
Jeff Moden - Sunday, April 29, 2018 11:15 AMbtio_3000 - Sunday, April 29, 2018 11:05 AMEirikur Eiriksson - Sunday, April 29, 2018 2:10 AMbtio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.CREATE TABLE dbo.[myTable](
[id] BIGINT IDENTITY( 1, 1 ) NOT NULL
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED ( [id] ),
[Date2] DATETIME NULL,
...
)
CREATE TABLE #t(
[id] INT IDENTITY NOT NULL,
[date1] DATETIME NULL,...
)
then there is an INSERT into #t from a table where [date1] is being populated from varchar(50)
and finally
UPDATE
dbo.[myTable] SET
..., [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...OUTPUT inserted.[id] INTO @open_id
FROM#temp t
JOIN...WHERE ...
pretty straight forward... sorry, I do not have an execution plan from the server where/when it happened and I was not able to duplicate an error even at that same server while running "manually" against the same dataset...
all the data in varchar(50) is in
2011-01-03 00:00:00.000
format, i.e. all the time part is actually zeros... Is it possible that somehow server considers it as a date still while inserting into temp table even though temp table says datetime? I know how it sounds
Thank you
Do you have the datetime optimization enabled for the server that's having the problem? If so, try turning it off and see what happens.
Thank you Jeff,
is it DATE_CORRELATION_OPTIMIZATION that you mean?
April 29, 2018 at 1:03 pm
btio_3000 - Sunday, April 29, 2018 12:04 PMJeff Moden - Sunday, April 29, 2018 11:15 AMbtio_3000 - Sunday, April 29, 2018 11:05 AMEirikur Eiriksson - Sunday, April 29, 2018 2:10 AMbtio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.CREATE TABLE dbo.[myTable](
[id] BIGINT IDENTITY( 1, 1 ) NOT NULL
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED ( [id] ),
[Date2] DATETIME NULL,
...
)
CREATE TABLE #t(
[id] INT IDENTITY NOT NULL,
[date1] DATETIME NULL,...
)
then there is an INSERT into #t from a table where [date1] is being populated from varchar(50)
and finally
UPDATE
dbo.[myTable] SET
..., [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...OUTPUT inserted.[id] INTO @open_id
FROM#temp t
JOIN...WHERE ...
pretty straight forward... sorry, I do not have an execution plan from the server where/when it happened and I was not able to duplicate an error even at that same server while running "manually" against the same dataset...
all the data in varchar(50) is in
2011-01-03 00:00:00.000
format, i.e. all the time part is actually zeros... Is it possible that somehow server considers it as a date still while inserting into temp table even though temp table says datetime? I know how it sounds
Thank you
Do you have the datetime optimization enabled for the server that's having the problem? If so, try turning it off and see what happens.
Thank you Jeff,
is it DATE_CORRELATION_OPTIMIZATION that you mean?
Yes. That's the one. Couldn't remember it off the top of my head.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2018 at 6:56 am
I would much sooner believe that the error came from the INSERT where a varchar(50) value didn't conform to a format necessary for a date or datetime value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 30, 2018 at 6:15 pm
sgmunson - Monday, April 30, 2018 6:56 AMI would much sooner believe that the error came from the INSERT where a varchar(50) value didn't conform to a format necessary for a date or datetime value.
Jeff Moden - Sunday, April 29, 2018 1:03 PMbtio_3000 - Sunday, April 29, 2018 12:04 PMJeff Moden - Sunday, April 29, 2018 11:15 AMbtio_3000 - Sunday, April 29, 2018 11:05 AMEirikur Eiriksson - Sunday, April 29, 2018 2:10 AMbtio_3000 - Saturday, April 28, 2018 9:52 AMThanks for replying,Not sure I understand...
like I said both [date2] in dbo.[myTable] and [date1] in #temp t ARE of DATETIME
though it still gives me an error...Thanks again
He he, missed the DATETIME column data type.
😎
Can you post the DDL for the tables, the execution plan and the full query please?
I have the hunch that there is an unintended or implicit conversion to date happening somewhere. This is particularly interesting as datetime has higher precedence than date.CREATE TABLE dbo.[myTable](
[id] BIGINT IDENTITY( 1, 1 ) NOT NULL
CONSTRAINT [PK_myTable] PRIMARY KEY NONCLUSTERED ( [id] ),
[Date2] DATETIME NULL,
...
)
CREATE TABLE #t(
[id] INT IDENTITY NOT NULL,
[date1] DATETIME NULL,...
)
then there is an INSERT into #t from a table where [date1] is being populated from varchar(50)
and finally
UPDATE
dbo.[myTable] SET
..., [date2] = DATEADD( MILLISECOND, -10, t.[date1] ),
...OUTPUT inserted.[id] INTO @open_id
FROM#temp t
JOIN...WHERE ...
pretty straight forward... sorry, I do not have an execution plan from the server where/when it happened and I was not able to duplicate an error even at that same server while running "manually" against the same dataset...
all the data in varchar(50) is in
2011-01-03 00:00:00.000
format, i.e. all the time part is actually zeros... Is it possible that somehow server considers it as a date still while inserting into temp table even though temp table says datetime? I know how it sounds
Thank you
Do you have the datetime optimization enabled for the server that's having the problem? If so, try turning it off and see what happens.
Thank you Jeff,
is it DATE_CORRELATION_OPTIMIZATION that you mean?
Yes. That's the one. Couldn't remember it off the top of my head.
Thank you Jeff,
Talked to DBA, not it's not the case... unfortunately, 🙂
sgmunson - Monday, April 30, 2018 6:56 AMI would much sooner believe that the error came from the INSERT where a varchar(50) value didn't conform to a format necessary for a date or datetime value.
Hi Steve,
the thing is that those varchar(50)'s are being inserted first into temporary table #t where [date1] is defined as DATETIME
still a mistery...
May 10, 2018 at 2:42 pm
( just an update: enforcing DATETIME by adding CONVERT( DATETIME, ... ) within DATEADD apparently fixed it... )
Thanks again
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply