CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Jodhpur -> Rajasthan -> INDIA
    Posts
    453

    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
    Leave Your Mark Wherever You Go
    http://www.danasoft.com/sig/d0153030Sig.jpg

  2. #2
    Join Date
    Oct 2001
    Location
    Melbourne, Australia
    Posts
    576

    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.

  3. #3
    Join Date
    Aug 2003
    Location
    London
    Posts
    515

    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
  •  





Click Here to Expand Forum to Full Width

Featured