Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 13th, 2004, 05:29 AM
Authorized User
 
Join Date: Aug 2003
Location: , , India.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to spraveens
Default Query regarding Date Ranges

Hi

   I need to write a query whereby I provide 2 dates and the results should have all the records with their dates with in the provided range specified by the 2 dates .How do I do this ?

Praveen

  #2 (permalink)  
Old January 13th, 2004, 05:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Code:
SELECT Fields
  FROM YourTable
 WHERE DateField BETWEEN EarlierDate AND LaterDate


Regards
Owain Williams
  #3 (permalink)  
Old January 13th, 2004, 06:03 AM
Authorized User
 
Join Date: Aug 2003
Location: , , India.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to spraveens
Default

Hi

   Here the variables fdate and tdate have the value assigned from a form using "request.form" , Now Iam using the following statement and it is returning null records even though there are some satifying records.Iam using Access database. The statement Iam using is :

sqlstmt= "select * from projectx where sdate BETWEEN "&fdate&" AND "&tdate

Set Rs = Con.Execute (sqlstmt)


pls help.


  #4 (permalink)  
Old January 13th, 2004, 06:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You need to quote your dates in your SQL statement:

Code:
sqlstmt = "SELECT * FROM projectx WHERE sdate BETWEEN '" & _
    fdate & "' AND '" & tdate & "'"
    For example, if fdate is 2003-01-01 and tdate is 2004-01-01 then the SQL statement that will be executed will be:

Code:
SELECT * FROM projectx WHERE sdate BETWEEN '2003-01-01' AND '2004-01-01'
You may also want to check that the dates in your variables are formatted correctly for your installation, for example US or UK date format.

Regards
Owain Williams
  #5 (permalink)  
Old January 13th, 2004, 07:18 AM
Authorized User
 
Join Date: Aug 2003
Location: , , India.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to spraveens
Default

Hi I did exactly as you quoted above and now Iam getting the following error.The query is returning an error which is

Error Type:
Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.
/sops/projectx/report.asp, line 21




  #6 (permalink)  
Old January 13th, 2004, 07:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Didn't realise you were using Access, in that case you need to wrap the dates in hashes (#) not quotes:

Code:
sqlstmt = "SELECT * FROM projectx WHERE sdate BETWEEN #" & _
    fdate & "# AND #" & tdate & "#"
    That should solve your problem.

Regards
Owain Williams
  #7 (permalink)  
Old January 13th, 2004, 07:56 AM
Authorized User
 
Join Date: Aug 2003
Location: , , India.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to spraveens
Default

God Bless You Mr.Owain

               I've been trying to figure out this problem for 6 hours now and finnaly you have given me the solution and the program is running fine now. I dont have words to thank you.

Regards.

praveen:D

  #8 (permalink)  
Old January 13th, 2004, 08:11 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A tip for the future, if you do not know how to write the query you want, fire up Access and create the query using the query designer. Then view the query in SQL view and copy the relevant parts into your code.

It is a bad way of learning SQL because Access uses a very funny dialect but it should help you if you get stuck.

In the mean time may I recommend you invest in a good SQL programming book? There are literally hundreds of these available in almost every SQL dialect imaginable. I have several myself and I must say that knowing the language really helps you understand what is going on and how to construct efficient and complex queries.

Regards
Owain Williams


Similar Threads
Thread Thread Starter Forum Replies Last Post
puzzle for date ranges navjot C# 1 October 16th, 2008 02:21 PM
Date Ranges nbuckwheat Access 1 December 12th, 2005 12:45 PM
date ranges yuqlin BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 2 December 23rd, 2004 05:23 PM
Query Returning Date Ranges rstelma SQL Server 2000 4 October 28th, 2003 08:57 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.