Dates, stored procedures and asp.net

Hi,
I am having problems with dates and timestamps...

I have a textbox that allows the user to enter a date - the format is ccyymmdd. I have validation on this to ensure it is in the correct format.

I pass this as a parameter to a sql server stored procedure. In the stored procedure that parameter is defined as datetime and the field in the database it is also datetime.

To cut a long story short...an exception is being thrown! I have convinced myself it is due to the date - everything else I have used before. I am not sure whether I need to do a conversion in SQL perhaps prior to insert but not sure how to do this in any case, or, whether I have to do something in the vb code before loading the parameter??

CREATE PROCEDURE [dateexample]
(@.SomeDate [datetime])

AS INSERT INTO [testdate]
([SomeDate])

VALUES
(@.SomeDate)
GO

Anyone had similar problems / suggestions ? If this is not posted in an appropriate forum I apologise like I say I'm not sure which side the solution lies...

Thanks to all who take the time to read thistry changing your SP to this one..

CREATE PROCEDURE dateexample (@.SomeDate datetime)
AS
begin
INSERT INTO testdate VALUES (@.SomeDate)
end
GO

|||Is the exception being thrown by SQL Server, or by your vb code?|||Just to make sure, since you mention timestamps in your original post. I hope your problem isn't that you're trying to use a timestamp field to store a date/time.
A timestamp is not a date or time field. It's a serial number. The name is somewhat misleading. It's one of the unfortunate legacy things from back in the Sybase days.

From SQL Books-on-line...

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server? may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server? 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.

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