February 4, 2015 at 2:17 pm
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.
select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions would be greatly appreciatted. Thank you!
February 4, 2015 at 3:56 pm
Your requirement is little confusing. Below are my questions.
1. How are you subtrating the 30 days from JE_DATE? JE_DATE is an integer as you said
2. The reason for your error because of JE_DATE >= DATEADD(DAY,-30,GETDATE())
JE_DATE = Integer
DATEADD(DAY,-30,GETDATE()) = returns date
Integer >= date (you are doing absolutely wrong syntax here)
If you could post your requirement clearly will be useful to help you out. also it would be good if you could share sample data and expected output
February 4, 2015 at 4:10 pm
gatorfe (2/4/2015)
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions would be greatly appreciatted. Thank you!
You say the date is stored as an integer, is the format of the integer YYYYMMDD? Or is it something else?
You also realize that dates should be stored as dates, not integers.
If the format is YYYYMMDD, try the following:
select * from GENERAL_LEDGER
where JE_DATE >= CAST(REPLACE(CONVERT(VARCHAR(10),DATEADD(DAY,-30,GETDATE()),102),'.','') AS INT)
February 4, 2015 at 6:11 pm
Lynn Pettis (2/4/2015)
gatorfe (2/4/2015)
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions would be greatly appreciatted. Thank you!
You say the date is stored as an integer, is the format of the integer YYYYMMDD? Or is it something else?
You also realize that dates should be stored as dates, not integers.
If the format is YYYYMMDD, try the following:
select * from GENERAL_LEDGER
where JE_DATE >= CAST(REPLACE(CONVERT(VARCHAR(10),DATEADD(DAY,-30,GETDATE()),102),'.','') AS INT)
OK. I'll bite Lynn. Is there any reason you didn't choose format conversion 112?
select * from (SELECT JE_DATE = 20151231) a
where JE_DATE >= CONVERT(CHAR(8), DATEADD(DAY,-30,GETDATE()), 112);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 4, 2015 at 7:10 pm
dwain.c (2/4/2015)
Lynn Pettis (2/4/2015)
gatorfe (2/4/2015)
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions would be greatly appreciatted. Thank you!
You say the date is stored as an integer, is the format of the integer YYYYMMDD? Or is it something else?
You also realize that dates should be stored as dates, not integers.
If the format is YYYYMMDD, try the following:
select * from GENERAL_LEDGER
where JE_DATE >= CAST(REPLACE(CONVERT(VARCHAR(10),DATEADD(DAY,-30,GETDATE()),102),'.','') AS INT)
OK. I'll bite Lynn. Is there any reason you didn't choose format conversion 112?
select * from (SELECT JE_DATE = 20151231) a
where JE_DATE >= CONVERT(CHAR(8), DATEADD(DAY,-30,GETDATE()), 112);
Missed it. Used 102 in a query here because I wanted the date separated when I did a cut/paste to excel. Thanks for reminding me of it.
Hey, 23 days and I am on my way home!
Lynn
February 4, 2015 at 7:36 pm
Lynn Pettis (2/4/2015)
dwain.c (2/4/2015)
Lynn Pettis (2/4/2015)
gatorfe (2/4/2015)
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
Any suggestions would be greatly appreciatted. Thank you!
You say the date is stored as an integer, is the format of the integer YYYYMMDD? Or is it something else?
You also realize that dates should be stored as dates, not integers.
If the format is YYYYMMDD, try the following:
select * from GENERAL_LEDGER
where JE_DATE >= CAST(REPLACE(CONVERT(VARCHAR(10),DATEADD(DAY,-30,GETDATE()),102),'.','') AS INT)
OK. I'll bite Lynn. Is there any reason you didn't choose format conversion 112?
select * from (SELECT JE_DATE = 20151231) a
where JE_DATE >= CONVERT(CHAR(8), DATEADD(DAY,-30,GETDATE()), 112);
Missed it. Used 102 in a query here because I wanted the date separated when I did a cut/paste to excel. Thanks for reminding me of it.
Hey, 23 days and I am on my way home!
Lynn
I get it! Coming home from that place would probably make me forget lots of stuff too!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 6, 2015 at 3:06 pm
Thank you. That worked!
February 6, 2015 at 3:09 pm
Glad it worked.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply