DateDiff / DateAdd - please help me:(

hello,

i have a Pictures table: PictureID, Name, Description, DateAdded (GETDATE() when insert), IsActive...

i need to make some stored procedures to show me the pictures added in last 24hours, in last 3 days, last 2 weeks and so on

the pictures added in database are active (available to be seen by users) only 1yaer after the date added

I tryied to make a stored procedure (in fact i maked a lots of them, for 1day 3 days 1 week 1 month), but i have a problem with that DateDiff and DateAdd

Here is what i tryied

CREATE PROCEDURE LastAdded_2monthsAgoASSELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAddedFROM PicturesWHERE (DATEDIFF(month, Pictures.DateAdded,GETDATE()) >= 0)AND (DATEDIFF(month, Pictures.DateAdded,GETDATE()) <= 2)ORDER BY DateAddedDesc

I have a feeling that is wrong, please make your own version and show me what i should write...I don't know what should be first the today date or the DateAdded...

i need to select the last added products from a specific interval of time...

Should i do something with that "1 year available" like  
WHERE (DATEDIFF(month,GETDATE(),DATEADD(year, 1, Products.DateAdded)) >= 0)AND (DATEDIFF(month,GETDATE(),DATEADD(year, 1, Products.DateAdded)) <= 2)
 
I am sure is a stupid thig up there...if you can, make your own version how you would do it and show me..
please help me

The simplest way to do this would be with a single stored procedure, and pass in the two dates to search between. (make sure you use a standard time scheme in order for this to be accurate, such as UTC/GMT.)

I suggest creating the start and end date in your [web] application, and your procedure should be like that below:

CREATE PROCEDURE LastAdded_2monthsAgo @.StartDatedatetime, @.EndDatedatetimeASSELECT Pictures.ProductID, Pictures.[Name], Pictures.[Description], Pictures.DateAddedFROM PicturesWHERE (Pictures.DateAddedBETWEEN @.StartDateAND @.EndDate)ORDER BY DateAddedDesc

Also note that you are using keywords for column names, which is why they show up blue. While this will still work in some cases, it is best to qualify the keyword as a column, which is why I used square brackets around those 2 column names. (Some people do this with every column.)

|||

LAST 24 HOURS:

WHERE Pictures.DateAdded >= DATEADD(DD,-1,GETDATE())

LAST 3 DAYS:

WHERE Pictures.DateAdded >= DATEADD(DD,-3,GETDATE())

LAST 2 WEEKS:

WHERE Pictures.DateAdded >= DATEADD(DD,-14,GETDATE())

SAMPLE SPROC:

CREATE PROCEDURE LastAdded_2monthsAgo
@.Number_Of_Days int
AS
SELECT Pictures.ProductID, Pictures.Name, Pictures.Description, Pictures.DateAdded
FROM Pictures
WHERE Pictures.DateAdded >= DATEADD(DD,@.Number_Of_Days, GetDate())
ORDER BY DateAddedDesc

Hope this helps,

|||

I should also clarify that when you are building your dates to search between, the first should have a time of 12:00 (midnight), and the end date should end at 11:59 PM. So if you are searching just for today's uploaded files, you would search between "9/28/2007 12:00:00 AM" and "9/28/2007 11:59:59 PM".

|||

It's usually better if you use midnight the next day as the end point (It avoids date/time accuracy issues).

Like;
WHERE MyDATE>='9/28/2007 12:00:00 AM' AND MyDate<'9/29/2007 12:00:00 AM'

or more concisely:

WHERE MyDate>='9/28/2007' AND MyDate<'9/29/2007'

|||

thank you to all for helping me

to show the pictures from the last 2 days or more i user the rhp_72 stored procedure,

and to show the pictures from a specific interval of time i used the ps2goat stored procedure

thank you, you resolved my problem