Monday, March 19, 2012

Datetime issue

I am trying to run a query to get items that expire within the next 3 days. I got this off of another section of this website, but it will not work in Microsoft SQL Server 2000-Any ideas? Thanks.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(CURRENT_DATE, INTERVAL 3 DAY) AND CURRENT_DATEYou need to look up the syntax of DATEADD in Books Online.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATE_ADD
(day, 3, CURRENT_DATE) AND CURRENT_DATE|||Okay-didn't think to do that-I had just done a datetime search-Would I be correct that the following would return anything within 7 days?

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN DATEADD (DD, 7, GETDATE()) AND GETDATE()

It isn't returning any data, so if I am doing correctly, I have something wrong in my insert statement.

Thanks.|||wherever you got that from, it doesn't work

not even if you convert the syntax to sql server syntax :)

the reason is, the dates need to be reversed

this will not work --

... where perishable_date between dateadd(day,3,current_date) and current_date

this might --

... where perishable_date between current_date and dateadd(day,3,current_date)

the reason i say "might" is because you need to find what microsoft sql server uses instead of the standard sql current_date function

;)|||whoops, you already found GETDATE() -- congratulations!

now just change your query around and you'll be in business|||I switched the dates and it works. Thank you.

SELECT *
FROM INVENTORY_TBL
WHERE PERISHABLE_DATE BETWEEN GETDATE() AND DATEADD(DD, 7, GETDATE())

No comments:

Post a Comment