Handling BETWEEN or >= and < with DATES

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

General SQL Server Forums

New to SQL Server Programming

Handling BETWEEN or >= and < with DATES

Author

Topic

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-01 : 15:25:27

I want to write some SQL to query a table from the first day of last month (or any month) to the last day. I have read that the way to do it is to say, for example, BETWEEN the first day of last month to the first day of the next month because if the time value is greater than midnight (on the TO date), it will not be in the result set.

I have 2 issues with that. The first is that if the date is EXACTLY midnight on the latter date, it would be included also, and I don't want that. The second issue is that if someone looks at the SQL, it could appear that the query was wrong. I would like to look at literally the first and last days (not first and last minus 1 second, etc.)
I know I can use the >= and < operators to solve the first issue, but what would be the best way to solve the second?

I know I haven't worded this very well (I am in a rush), but hopefully someone can help me with it. It may be that there is some pre-written functions that will work with that. That would be fine if I knew what they were.

Thank you.

Duane

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 15:29:41

not very clear to me...why dont you take an example...whats wrong with

date >= '06/01/2009' and date < '07/01/2009'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 15:35:37

quote:
Originally posted by rohitkumar

...whats wrong with
date >= '06/01/2009' and date < '07/01/2009'

That is his question too

No, you're never too old to Yak'n'Roll if you're too young to die.

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-01 : 15:56:13

I just wanted to be able to use something like MyDate BETWEEN >= '06/01/2009' AND '06/30/2009' as it may be more clear to a casual observer. If, in English we want all appointments in June, for example, we are going to say "List all appointments between June 1, 2009 and June 30, 2009." We would not say, "List all appointments on or after June 1, 2009 and before July 1, 2009" Which would be more clear? I know I can write it "date >= '06/01/2009' and date < '07/01/2009'". I was just hoping for clarity that I could create a function if one were not already written that would allow me to compare dates this way, especially since BETWEEN is technically inclusive of both ends. I suppose that would only work if date and time were separate column. But I thought there might be a way to extract the date part out of a column and compare the June 30 portion of that datetime column. Thanks for the input anyway.

Duane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-01 : 16:05:38

Not to be snide, but if some ready the code can't figure out what: date >= '06/01/2009' and date < '07/01/2009' means, then they shouldn't be reading it. That form of doing a date range to perfectly acceptable and my personal preference.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-01 : 16:07:27

quote:
Originally posted by Lamprey

Not to be snide, but if some ready the code can't figure out what: date >= '06/01/2009' and date < '07/01/2009' means, then they shouldn't be reading it. That form of doing a date range to perfectly acceptable and my personal preference.

agree!

No, you're never too old to Yak'n'Roll if you're too young to die.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 16:09:18

well, you can extract the date part of a column
SELECT MyDate, DATEADD(DD, 0, DATEDIFF(DD, 0, Mydate)) from MyTable

but then while doing
DATEADD(DD, 0, DATEDIFF(DD, 0, Mydate)) < '06/30/2009'
you'll skip all records for 06/30 in your table

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 16:11:02

and I agree to what Lamprey said (and webfred agreed to)

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-01 : 16:23:05

Fine, but you have to admit I have a point. I guess I'll just go along with it. By the way, there are all too many people reading code that shouldn't be, especially SQL, but I have no control over that. I was just trying to pre-emptively flush out some potential confusion. I also had some statements I found on the internet that return the last date of the month, year, etc. and didn't want to rewrite and in fact was interested in why they are written like this in the first place if they can't really be used in a practial way:

DECLARE @MONTH_FIRST_DAY AS DATETIME
SELECT @MONTH_FIRST_DAY = DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
DECLARE @MONTH_LAST_DAY AS DATETIME
SELECT @MONTH_LAST_DAY = DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
SELECT DISTINCT MOD_DATE FROM PT_BASIC
WHERE MOD_DATE BETWEEN @LAST_MONTH_FIRST_DAY AND @LAST_MONTH_LAST_DAY
ORDER BY MOD_DATE

I put the variables in, but that is what I found Googling. I didn't think I would have to re-invent the wheel. I am fairly new at this kind of thing. I someone wants to take a stab at helping me with the second @MONTH_LAST_DAY so it will get everything on that day, I would appreciate it. Thanks again.

Duane

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 16:28:18

I would use DATEDIFF

But you need to tell us what version you are using...worry about time components, I would say you are pre 2k8

And you're worrying about someone reading your code?

And looking at your recent code you want rows with dates of today or yesterday? -- see simple business requirements

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-01 : 16:38:33

I mean for all last month:
DECLARE @LAST_MONTH_FIRST_DAY AS SMALLDATETIME
SELECT @LAST_MONTH_FIRST_DAY = DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
DECLARE @LAST_MONTH_LAST_DAY AS SMALLDATETIME
SELECT @LAST_MONTH_LAST_DAY = DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
And it is SQL Server 2005 and there are those who read it. Yes, there is.

Duane

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 16:41:04

[CODE]
SELECT @LAST_MONTH_FIRST_DAY = DATEADD(MM, DATEDIFF(MM, 0, GETDATE())-1, 0)
SELECT @LAST_MONTH_LAST_DAY = DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0))

...WHERE MOD_DATE BETWEEN @LAST_MONTH_FIRST_DAY AND @LAST_MONTH_LAST_DAY
[/CODE]

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 16:49:29

[code]
DECLARE @MOD_DATE datetime, @GetDate datetime

SELECT GetDate()

-- Produces 2009-07-01 16:43:23.637 as of this posting

SET @MOD_DATE = '2009-06-15'
SET @Getdate = '2009-07-01 16:43:23.637'

SELECT DATEDIFF(mm,@MOD_DATE,@GetDate)

-- Are we equal to 1? Yes? Then we get dates from a month ago

SET @MOD_DATE = '2009-06-01'
SELECT DATEDIFF(mm,@MOD_DATE,@GetDate)
-- How About now?

-- Yes Again

-- So you're predicate should be

WHERE DATEDIFF(mm,MOD_DATE,GetDate()) = 1

-- If they can't read it, good for you

[/code]

Cut and paste to see it work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-01 : 17:23:11

What rohitkumar said looks pretty simple. I will give that a try. I also copied and pasted X00548's response and it did work but I am having trouble figuring out how to put it in a query. I want to SET @MOD_DATE = "SELECT MOD_DATE FROM PT_BASIC". That syntax and a few others I have tried isn't working. But thanks for the responses. They are very helpful.

Duane

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 20:31:04

Seems like you want to do dynamic sql, and I would ask why?

Is there any reason to?

Also, you should do a show plan to see which performs better

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-01 : 20:51:19

Queries for a date range should normally be in this form:

where MyDatetimeColumn >= @StartDatetime  and MyDatetimeColumn < @EndDatetime

In other words greater than or equal to the first point in time that we know that we want, and less than the first point in time that we don't want.

There is never any guarantee that the precision of time intervals will stay the same in future releases, so it is unrealistic to code end time as "2009-06-30 23:59:59.997", even if this is the last possible moment that we want to select with the current version of SQL Server. Just code it as less than '2009-07-01'.

You should not use a query that uses your date column in a function call because it will prevent SQL Server from using an index on that column.


-- Query to select rows for the current month
select
        *
from
        MyTable
where
        -- Greater than or equal to first day of month
        MyDate >= dateadd(month,datediff(month,0,getdate()),0)  and
        -- Less than the first day of next month
        MyDate <  dateadd(month,datediff(month,0,getdate())+1,0)


-- Query to select rows for the last month
select
        *
from
        MyTable
where
        -- Greater than or equal to first day of last month
        MyDate >= dateadd(month,datediff(month,0,getdate())-1,0)  and
        -- Less than the first day of current month
        MyDate <  dateadd(month,datediff(month,0,getdate()),0)

The following link provides links to a lot of information about using date and time in SQL Server.

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-01 : 21:11:55

quote:
Originally posted by Michael Valentine Jones
You should not use a query that uses your date column in a function call because it will prevent SQL Server from using an index on that column.

Well there always is that...Thank MVJ

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

duanecwilson
Constraint Violating Yak Guru

273 Posts

Posted - 2009-07-02 : 09:16:59

This was excellent. I have obviously overcomplicated this, but I did learn a lot. Also, that statement about the index was worth all of it by itself. I probably would have scratched my head for hours on that one if I had run into it. I had never thought of it.
Thank you all for your input.

Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources