Problems with Date Searches

Question

Post reply

Problems with Date Searches

thomas.hill

Mr or Mrs. 500

Points: 504
More actions
February 23, 2010 at 8:04 pm

#217251

Greetings,
You all have helped tremendously in the past with my projects. I have now inherited a large, recently created database in SQL 2008 that went awry somehow - Though it may sound simpler to redo the dbase, but 35,000 records were populated into the tables and queries written off that data...
Here's my question.
I need to extract a specific date range from a date field entitled
DateEnd, from a table called PERSONS
I need to extract the dates between specific years.
For example:
SELECT ID, Last, First, MI, WorkLocation, DateStart DateEnd
FROM PERSONS
Where DATEnd >=1/01/2009 AND <=12/31/2009
I have used several BETWEEN statements, even trying to extract just the yyyy out of the date string, but no luck.
BETWEEN DATEEND '01/01/2009' AND '12/31/2009'
Dateend datepart(yyyy)2009))
And trying a random user input:
where Dateend >=@dateend (which returns everything after for the year selected plus the following years, which I don't need)
I have also specified:
Where Dateend ='2009' But this simply converts everything in the query to 01/01/2009
The DATEEND field in the Persons is simply formatted as "DATE" but it is displaying Date, and Time in hh:mm:ss:ms etc....
Is this enough info for you to work with? i am really looking for a solution to BETWEEN certain date query.
Thank you for any help and keep up the good work .
Tom

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

It has often been found to be easier to use a different way than using BETWEEN when working with date ranges:

WHERE DateEnd >= '1/1/2009' and DateEnd < '1/1/2010' will get everything in 2009.

When using BETWEEN, you would have to go:

WHERE DateEnd BETWEEN '1/1/2009' and '12/31/2009 23:59:59.997'

and now, with the enhanced capacity of the datetime2 datatype in SQL 2008, you might need need to go:

WHERE DateEnd BETWEEN '1/1/2009' and '12/31/2009 23:59:59.9999999'

but if you're using a datetime datatype, this would round up to 1/1/2010, which could get dates in 2010 with no time. So the easiest way is to use the first way all the time, and you don't have to worry about it.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 2

Outstanding,

Thank you so much for the help. I'll give it a try here in a few..

Tom

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 3

Wayne,

I tried what you sent me and ran into just a small problem... but I think I fixed it...

Here's what you sent: WHERE DateEnd >= '1/1/2009' and DateEnd < '1/1/2010'

I ran into an error whree the > and the &lt were causing a script error referencing a Non-Boolean statement.

I changed it to:

WHERE DateEnd >= '1/1/2009' and DateEnd <= '1/1/2010'

And it seems to work...

What do you think? It kind of seems like one of the things I did earlier that didn't work..... (I'm laughing to myself).

Anyway, you led me down the right path none-the-less.

Take care.

Tom

Now if I could just figure out how to add these search fields to an ASP page I'd be tremendously happy!

mike 84768 Right there with Babe Points: 792 More actions · Answer 4

You could also use the Datepart function...returns an integer representation of the year.

WHERE Datepart(yyyy,DateEnd) = 2009

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

thomas.hill (2/23/2010)
Wayne,
I tried what you sent me and ran into just a small problem... but I think I fixed it...
Here's what you sent: WHERE DateEnd >= '1/1/2009' and DateEnd < '1/1/2010'
I ran into an error whree the > and the &lt were causing a script error referencing a Non-Boolean statement.
I changed it to:
WHERE DateEnd >= '1/1/2009' and DateEnd <= '1/1/2010'
And it seems to work...
What do you think? It kind of seems like one of the things I did earlier that didn't work..... (I'm laughing to myself).
Anyway, you led me down the right path none-the-less.
Take care.
Tom
Now if I could just figure out how to add these search fields to an ASP page I'd be tremendously happy!

There is something terribly wrong if you had to make that change. You do not want what you changed it to because it will return rows outside of 2009. You need to figure out why Wayne's code didn't work for you because his code is the correct way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 6

mike 84768 (2/24/2010)
You could also use the Datepart function...returns an integer representation of the year.
WHERE Datepart(yyyy,DateEnd) = 2009

The problem with this approach is that you won't be able to use any index on the DateEnd field.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

thomas.hill (2/23/2010)
I changed it to:
WHERE DateEnd >= '1/1/2009' and DateEnd <= '1/1/2010'

This means that you will return any DateEnd value in 2009, AND any DateEnd value of "1/1/2010 00:00:00.000". Nope, you need to change "<=" to "<" to get just 2009 values.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 8

Instead of

WHERE Date BETWEEN '1/1/2009' and '12/31/2009 23:59:59.997'

I'd rather use

WHERE Date >= '20090101' and Date<'20100101'

The main reason for date formatting YMD instead of M/D/Y is to have no influence from the DATEFORMAT value.

If you'd run

SET DATEFORMAT dmy

prior to your statement it'll fail with an "out-of-range datetime value" error.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ekant_alone SSCrazy Points: 2423 More actions · Answer 9

i am little curious

can't we use 'year' function in where clause?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

ekant_alone (2/24/2010)
i am little curious
can't we use 'year' function in where clause?

You could do this:

mike 84768 (2/24/2010)
You could also use the Datepart function...returns an integer representation of the year.
WHERE Datepart(yyyy,DateEnd) = 2009

which is the same as

WHERE YEAR(DateEnd) = 2009;

Problem is the same, however, if there is an index on DateEnd, you won't use it.

WayneS SSC Guru Points: 95460 More actions · Answer 11

ekant_alone (2/24/2010)
i am little curious
can't we use 'year' function in where clause?

Yes, you can use it.

But let me show you why you shouldn't.

First, let's create a test table and populate it with 1,000,000 unique dates

if OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (ColA datetime PRIMARY KEY CLUSTERED)

;WITH TALLY (N) AS

(

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (select 1))

FROM master.sys.all_columns c1, master.sys.all_columns c2, master.sys.all_columns c3, master.sys.all_columns c4

)

INSERT INTO #Temp

select DATEADD(MINUTE, -1*N, GETDATE())

FROM TALLY

Now, let's run two sql statements. Set the query to display the actual execution plan.

First, run this two select statements, the first with the year function and the second with a specified date range:

select ColA

FROM #Temp

where YEAR(ColA) = 2010

select ColA

FROM #Temp

where ColA >= '20100101'

AND ColA < '20110101'

Now check out the execution plan for these queries as shown in CIScan.sqlplan (attached below)

(Notice that the SCAN takes 93% of the time, while the SEEK takes 7%.)

Basically, if you use any function on a column, you remove the ability for SQL to seek on that column for that query.

Edit: just can't get these plans to show in the post like Paul can... how does he do that magic?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ekant_alone SSCrazy Points: 2423 More actions · Answer 12

Thank you so much.

This execution plan is really shocking to me. I never thought about that before and infect i was using year function for my cause.

Thank you so much for this. I really appreciate your efforts.

thomas.hill Mr or Mrs. 500 Points: 504 More actions · Answer 13

Thank you all for taking so much of your time to help.

Jeff, I did figure out the code this morning - but I did type the code exactly as I illustrated by Wayne last night, except that my computer, for some reason, initally displayed the < > symbols as a string character I think. They were both displayed with the AND symbol "&" an "l" and a small '"b" (think). I have a feeling it was the back up computer I was using last night. The code was changed back to represent what Wayne sent and it worked fine.

I realized this when I went back over the email Wayne sent last night on my development server this morning and found that the >< characters were present where they were just computer code before. I hope this makes sense, but all is well.

Thanks again,

Tom

WayneS SSC Guru Points: 95460 More actions · Answer 14

Tom, I think that the "<" and ">", when inside the sql code window, get converted into xml tags: "& lt;" and "& gt;" (without the spaces). I'll bet this is what the confusion is about...

Edit: put a space after the & so it wouldn't show them as <>

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2