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
 
Old March 9th, 2004, 06:25 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default Working with Dates in SPROC

Hello Everyone and thanks for your help in advance. I am trying to write a stored procedure that will return records between two given dates. For example:

Select * From myTable Where OrderDate >= BegDate AND =< EndDate

With the intention of returning all records equal to or between the starting and ending dates. However, when I pass the same date to both the BegDate and EndDate paramteters, I receive no records. I am really not sure why this is the case. Any help on how to solve this would be greatly appreciated. Thanks.

 
Old March 9th, 2004, 06:32 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Are you using SQL Server?

I could be wrong, but should you use the column name for the second clause as well?, e.g.:

Select * From myTable Where OrderDate >= BegDate AND OrderDate =< EndDate

Alternatively, you could use BETWEEN:

Select * From myTable Where OrderDate BETWEEN BegDate AND EndDate

Between is inclusive, so it matches dates equal to BegDate as well.

Cheers,

Imar



---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 9th, 2004, 08:02 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Imar,

Yes, I am using the same column name for each as you noted. I also tried the BETWEEN and it returned nothing as well. The SQL Server column is of datetime type.

 
Old March 10th, 2004, 04:11 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

I think this is one of those SQL Server date thingies again. If you take a look at what you exactly you're querying for, things become clearer.

If you query for something that is larger than or equal to, and smaller than or equal to, you end up with only equal to, right? (If it's larger, it can't be smaller at the same time). So, in effect, this is what you query for:

WHERE OrderDate = MyDate

I assume that the OrderDate in your SQL Server table is a date with time info as well (3/10/2004 5:15:39 PM, for example), and that MyDate is just a day, month and year, defaulting to 00:00:00 for the time part
.
To fix this, you'll need to convert your OrderDate to a varchar, and then convert it back to a date. This will cause OrderDate to have a time element of 00:00:00 as well, so your query will work. Use something like this:

CONVERT(datetime, CONVERT(varchar(10), YourDateHere, 111))

This first converts the date to a varchar of 10 characters, so you end up with something like: 2004/04/10 That varchar is then converted back into a date, resulting in 2004-03-10 00:00:00.000

There may be quicker ways to do the conversion, but at least this will get you what you need.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old March 10th, 2004, 03:56 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Right as always Imar. I actually solde it by appending "12:00:00 am" to the beginning date parameter and "12:59:59 pm" to the ending date before passing them to the SPROC. Seems to work fine.

As always, thanks for the help.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with sproc Bob Bedell SQL Server 2005 10 January 20th, 2008 08:52 AM
dynamic sproc sabry SQL Language 1 August 25th, 2006 06:23 AM
ComapreValidator is not working with Dates kapi.goel ASP.NET 2.0 Basics 1 April 20th, 2006 01:50 PM
When a sproc or table was Last Used dlandolin SQL Server 2000 1 September 29th, 2005 08:09 PM
Sproc not Working with WildCard hugh@kmcnetwork.com SQL Server 2000 7 July 15th, 2005 05:38 AM





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