Date Range Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Range Question

Author

Topic

aawan
Starting Member

24 Posts

Posted - 2005-09-19 : 17:56:03

Hi, I was wondering if someone could help me with my query, with regards to Date Conversion.

I'm running queries, based on a date range, but my dates are in datetime format, but my ranges are like '12/01/2004 - 03/04/2005'. You know, without the time.

This is the offending query:

SELECT PolicyNumber, RequestDate
FROM Customers
WHERE (CONVERT(char(10), RequestDate, 101) BETWEEN '09/01/2004' AND '09/19/2005')
ORDER BY PolicyNumber DESC

I have records between January of this year and Sep. Somehow it is ignoring the year, thinking that my range is between 9/1/05 and 9/19/05. Instead of 9/1/04 to 9/19/05.

Is there something wrong with my conversion function?

Any help is greatly appreciated.
In the event that this is an issue that was previously brought up in the forum, I would be grateful if someone could post a link to that thread, and I apologize for taking up bandwidth.

Thanks,
Ali

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-19 : 19:08:55

If the column is datetime datatype then just compare it as date. SQL will implicitly convert '09/01/2004' for ya.

declare @dates table (policyNumber int, requestDate datetime)
insert into @dates
select 1,'2005-07-13 00:08:33.620' union
select 2,'2005-09-20 00:09:54.073' union
select 3,'2005-08-16 00:00:00:000'

SELECT policyNumber, RequestDate
FROM @dates
WHERE RequestDate BETWEEN '09/01/2004' AND '09/19/2005'

-- check it
select 	cast('09/01/2004' as datetime),
	cast('09/19/2005' as varchar)

Nathan Skerl

aawan
Starting Member

24 Posts

Posted - 2005-09-19 : 19:28:59

Thanks for the reply Nathan.
However that does not work.

If I have a date in the database which is
9/19/2005 10:32:17 AM

Then this query will return no results.

SELECT policyNumber, RequestDate
FROM @dates
WHERE RequestDate BETWEEN '09/01/2004' AND '09/19/2005'

What I was looking for was a solution in which I can do a conversion in the select statement to the Request Date to do an easy date range.

The dates in the range are being passed from a web form.
Request Date is a datetime in the database.

Ali

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-19 : 23:51:11

You are not getting any results because 9/19/2005 10:32:17 AM is outside of the range of '09/01/2004' to '09/19/2005' in your query.

'09/19/2005' will be converted by SQL Server to a datetime of 2005/09/19 00:00:00.000 and that is before 9/19/2005 10:32:17 AM, so you are not getting any results.

Date queries should be in the form of
Date >= Start Datetime and Date < End Datetime
See example:


SELECT
	policyNumber,
	RequestDate
FROM
	@dates
WHERE
	RequestDate >= '2004/09/01' AND
	RequestDate <  '2005/09/20'

quote:
Originally posted by aawan

Thanks for the reply Nathan.
However that does not work.

If I have a date in the database which is
9/19/2005 10:32:17 AM

Then this query will return no results.

SELECT policyNumber, RequestDate
FROM @dates
WHERE RequestDate BETWEEN '09/01/2004' AND '09/19/2005'

What I was looking for was a solution in which I can do a conversion in the select statement to the Request Date to do an easy date range.

The dates in the range are being passed from a web form.
Request Date is a datetime in the database.

Ali

CODO ERGO SUM

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-21 : 12:48:39

Ahh yes. Good catch Michael. I should have mentioned that.

Heres a good read on the subject:

[url]http://www.windowsitpro.com/SQLServer/Article/ArticleID/37713/37713.html[/url]

Nathan Skerl

aawan
Starting Member

24 Posts

Posted - 2005-09-21 : 13:00:15

Great Article Nathan.
Also, thanks to Michael.
Yeah I ended up using a query like that, however I add the time to the To Date.

So my range becomes WHERE
RequestDate >= '2004/09/01' AND
RequestDate < '2005/09/20 23:59:59.999'

Thank you and good night.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-21 : 13:50:32

It might be simpler to just code the date range this way:
where
RequestDate >= '2004/09/01' AND
-- Add 1 day to the end date
RequestDate < dateadd(dd,1,'2005/09/19')

23:59:59.999 actually rounds up to the next day. This code shows what happens. If it didn't round it up, you would actually have a logical error, bacause you would be excluding a valid time value. The end value of the date range should always be a date that should not be included.

select [Date] = convert(datetime,'2005/09/20 23:59:59.999')

Date
------------------------
2005-09-21 00:00:00.000

(1 row(s) affected)

quote:
Originally posted by aawan

Great Article Nathan.
Also, thanks to Michael.
Yeah I ended up using a query like that, however I add the time to the To Date.

So my range becomes WHERE
RequestDate >= '2004/09/01' AND
RequestDate < '2005/09/20 23:59:59.999'

Thank you and good night.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources