Problems with Date Searches
-
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
-
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 -
Outstanding,
Thank you so much for the help. I'll give it a try here in a few..
Tom
-
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 < 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!
-
You could also use the Datepart function...returns an integer representation of the year.
WHERE Datepart(yyyy,DateEnd) = 2009
-
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 < 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)
Intro to Tally Tables and Functions -
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 -
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 -
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 dmyprior to your statement it'll fail with an "out-of-range datetime value" error.
-
i am little curious
can't we use 'year' function in where clause?
-
ekant_alone (2/24/2010)
i am little curiouscan'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.
-
ekant_alone (2/24/2010)
i am little curiouscan'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 #TempCREATE 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 ColAFROM #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 -
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.
-
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
-
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply