-
November 9th, 2004, 03:24 AM
#1
Datetime related SQL problem( with MS SQL server 2000) and Reporting issue
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
-
November 9th, 2004, 04:29 PM
#2
Re: Datetime related SQL problem( with MS SQL server 2000) and Reporting issue
not sure what you mean by queries 2 and 3, but when referring to dates in SQL you enclose them in '# ' characters:
Code:
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.
-
November 10th, 2004, 05:02 AM
#3
Re: Datetime related SQL problem( with MS SQL server 2000) and Reporting issue
You could make use of a stored procedure for this, with a couple of parameters...
eg;
Code:
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|