The datepart microsecond is not supported by date function dateadd for data type date.

Question

Post reply

The datepart microsecond is not supported by date function dateadd for data type date.

hoseam

SSCertifiable

Points: 5533
More actions
March 15, 2016 at 7:47 am

#314109

DECLARE @End_Date DATE
,@The_Date DATE
SET @NetGross = 209 --Set the NetGross value here
SET @CommissionPercentage = 5 --Set the CommissionPercentage here
SET @The_Date = '2016-03-01'
SET @End_Date = DATEADD(mcs ,-1 ,DATEADD(DAY ,7 ,@The_Date ))
I am getting this error "The datepart microsecond is not supported by date function dateadd for data type date."
Can anyone help me here

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DATE is a date without a time component. Therefore you can't subtract microseconds from it.

SET @End_Date = DATEADD(DAY ,7 ,@The_Date)

and then instead of using BETWEEN @Start_Date and @End_Date, rather use inequalities and Column >= @Start_Date AND Column < @EndDate. That way you don't have to worry about what the smallest piece you can remove from the variable is (which isn't 1 microsecond for any of the datetime datatypes with their default precisions)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Y.B. SSChampion Points: 11649 More actions · Answer 2

hoseam (3/15/2016)
DECLARE @End_Date DATE
,@The_Date DATE
SET @NetGross = 209 --Set the NetGross value here
SET @CommissionPercentage = 5 --Set the CommissionPercentage here
SET @The_Date = '2016-03-01'
SET @End_Date = DATEADD(mcs ,-1 ,DATEADD(DAY ,7 ,@The_Date ))
I am getting this error "The datepart microsecond is not supported by date function dateadd for data type date."
Can anyone help me here

The Date data type only contains the date and not the time. Heck for micoroseconds you'd have to go to a DATETIME2 data type.

DECLARE @End_Date DATETIME2, @The_Date DATETIME2

SET @The_Date = '2016-03-01'

SET @End_Date = DATEADD(mcs ,-1 ,DATEADD(DAY ,7 ,@The_Date ))

Is this what you are really trying to do?

SELECT quote FROM brain WHERE original = 1
0 rows returned

Lowell SSC Guru Points: 323518 More actions · Answer 3

the error is really straight forward.

just like an integer cannot have decimal information, the DATE datatype does not support sub-minute time increments.

choose a datype that is appropriate; ; DATETIME can suppurt incrments of 3 milliseconds; if you need tighter than that, you have to use DATETIME2

/*

--Results

Date_Datatype Datetime_Datatype Datetime2_Datatype

2016-03-15 2016-03-15 09:54:02.473 2016-03-15 09:54:02.4730000

*/

SELECT

CONVERT(date,getdate()) As Date_Datatype,

CONVERT(datetime,getdate()) As Datetime_Datatype,

CONVERT(datetime2,getdate()) As Datetime2_Datatype

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

yb751 (3/15/2016)
Is this what you are really trying to do?

Probably not. He's probably trying to use an = for the upper end of a date range, which isn't the best of ideas no matter what the data type is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

hoseam (3/15/2016)
DECLARE @End_Date DATE
,@The_Date DATE
SET @NetGross = 209 --Set the NetGross value here
SET @CommissionPercentage = 5 --Set the CommissionPercentage here
SET @The_Date = '2016-03-01'
SET @End_Date = DATEADD(mcs ,-1 ,DATEADD(DAY ,7 ,@The_Date ))
I am getting this error "The datepart microsecond is not supported by date function dateadd for data type date."
Can anyone help me here

Others have answered your question on the error.

I will explicitly ask a much more important question: what are you actually trying to achieve/do here? What will you do with that @End_Date variable?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service