datetime in in sql query

Hi

I am trying to write a query involve parameters. For example, the query:

Select * from myTable

wheremyDateTime=@.dt;

If I run the query, I was asked to enter value for the parameter. The query can be generated, however I can't save it, the error message says: Must declare the variable @.dt. When I tried to declare it, the system doesn't support it. I am using SQL Server Managerment Studio 2005.

I also tried the query without the parameter:

Select * from myTable

wheremyDateTime=31/07/2007;

But it didn't return record for any datetime format.

Could anyone help please? I just want to get some records filtered by a certain DateTime.

Claire

Are you trying to bulit it as a view or a stored procedure? Its not possible to create a View with paramters.

Stored Proc would look like:

CREATEPROCEDURE sp_MyStoredProc
@.dtasDateTime
AS

BEGIN

SELECT
*
FROM
myTable
WHERE
myDateTime=@.dt

END

To run it you wold have to execute it:

exec sp_MyStoredProc GetDate()

|||

Hi,

You will have to check how are the dates stored in your column. If they are stored as MM/dd/yyyy hh:mm:ss AMPM then you will have to use a Convert function as shown at the end of this post

For your first query, you will need to declare your variable using this

Declare @.dt datetime

Select * from myTable

wheremyDateTime=@.dt;

For your second query, if only the date is stored then

Select * from myTable

wheremyDateTime='31/07/2007'

To understand this better, try these

selectgetdate()

SELECTDATEADD(dd, 0,DATEDIFF(dd, 0,GETDATE()))

SELECTCONVERT(VARCHAR(10),GETDATE(),111)

Check this link

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--