Datetime related SQL problem( with MS SQL server 2000) and Reporting issue

[d0153030]

Hello

I want to execute following query with MSSqlServer 2000 as database.

SELECT * FROM TABLENAME WHERE CurrentDate = 'Date Selected by user'

Well now my date field in the table is smalldatetime

I am using DTPicker on the VB form to get the date: So i am trying to get the date from it as DTPicker1.value


Second query:
I want to sort records on tha basis of dates i.e "between and" sequence

Third query:
I want to get the datareports based on teh date and time

How to do this?

Help me out



I want to get the report

[Zeb]

not sure what you mean by queries 2 and 3, but when referring to dates in SQL you enclose them in '# ' characters:

sql = "SELECT * FROM TABLENAME WHERE CurrentDate = #" & dtpicker1.value & "#"

this may depend on your region settings (and I'm not sure if it is the local machine, the server or both) but dates generally have to be in "mm/dd/yyyy" format also for sql server, and i've never used the date/time picker so you may have some issues there.

[Dmorley]

You could make use of a stored procedure for this, with a couple of parameters...
eg;

CREATE PROCEDURE spGetInformation(@Date1 SMALLDATETIME, @Date2 SMALLDATETIME) AS

/* Return all the information between date1 and date2, sorted by the date fied */
	SELECT 
		* 
	FROM 
		TABLENAME 
	WHERE 
		CurrentDate BETWEEN @Date1 AND @Date2
	ORDER BY	
		CurrentDate

Run this from Query Analyzer against your database to create the sproc. Then from your VB client or data report, you can use the sproc as your datasource, or make use of the ADODB.Command object.