July 10, 2013 at 1:33 pm
Believe me when I say that I have googled this and searched this forum up and down and either don't understand or can't find the answer I am looking for. I have a simple SQL 2008 database that receives names, companies and the date when the company representatives visit request expires. I am trying to write a query that will return rows of reps whose visit request expires in 30 days. What I have written is
SELECT vrExpdate - 30 DAYS
FROM mydatabase
It returns an error stating Operand type clash: date is incompatible with int. The data type for vrExpdate is "Date". I understand that an integer isn't compatible with a date but what I don't understand is how to write this query. I have tried unsuccessfully using every "DATE" function I can find...from DATEADD to DATEDIFF and everything in between.
Any help will be greatly appreciated.
July 10, 2013 at 1:39 pm
shane.stitts.ctr (7/10/2013)
Believe me when I say that I have googled this and searched this forum up and down and either don't understand or can't find the answer I am looking for. I have a simple SQL 2008 database that receives names, companies and the date when the company representatives visit request expires. I am trying to write a query that will return rows of reps whose visit request expires in 30 days. What I have written isSELECT vrExpdate - 30 DAYS
FROM mydatabase
It returns an error stating Operand type clash: date is incompatible with int. The data type for vrExpdate is "Date". I understand that an integer isn't compatible with a date but what I don't understand is how to write this query. I have tried unsuccessfully using every "DATE" function I can find...from DATEADD to DATEDIFF and everything in between.
Any help will be greatly appreciated.
SELECT [Your Columns Here]
FROM mydatabase
WHERE vrExpdate < DATEADD(DAY, 30, getdate())
I am not 100% certain of what you want but I think you want to find any rows where vrExpdate is less than 30 days after the current date?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 1:47 pm
Thanks for the help, but to clarify my question some, I am trying to return the list of names of people who will be expiring in 30 days. (I did add "name" to my SELECT statement). So if for example, I had a visit request that was set to expire August 31, 2013, my query would notify me on August 2 that I will be expiring soon. I have the stored procedure written for that, not sure if it works because I can't get the query right. I like to try to figure things out before I ask.
The query you gave me returned dates that have already passed...forgot to mention that.
July 10, 2013 at 1:59 pm
I'm sorry. Your code is exactly right and I had it right at one point earlier. My database just has garbage in it trying to make the forms work which won't be there when it goes into production so of course it is going to pull all the old information.
I have been defeated today if you can't tell.
Again, thanks for the help.
July 10, 2013 at 1:59 pm
shane.stitts.ctr (7/10/2013)
Thanks for the help, but to clarify my question some, I am trying to return the list of names of people who will be expiring in 30 days. (I did add "name" to my SELECT statement). So if for example, I had a visit request that was set to expire August 31, 2013, my query would notify me on August 2 that I will be expiring soon. I have the stored procedure written for that, not sure if it works because I can't get the query right. I like to try to figure things out before I ask.The query you gave me returned dates that have already passed...forgot to mention that.
Simply add "AND vrDate > getdate()" and it will not return any that expired earlier than the current date. If this is for a daily process or something then change the < in the original query to an = .
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply