November 17, 2008 at 1:46 pm
This has to be a very basic question but it just puzzles me. I want to select all the dates less than or equal to a given date, 01/01/2009.
The brute force methods I have used are
Where EndDt <= '01/01/2009 23:59' for smalldatetime
and
Where EndDt <='01/01/2009 13:59:59:99:99' for datetime
Or
where EndDt < '01/02/2009' but this requires me to increase the date I want to compare by one day.
What is the best way to do this?
November 17, 2008 at 1:53 pm
mpdillon (11/17/2008)
This has to be a very basic question but it just puzzles me. I want to select all the dates less than or equal to a given date, 01/01/2009.The brute force methods I have used are
Where EndDt <= '01/01/2009 23:59' for smalldatetime
and
Where EndDt <='01/01/2009 13:59:59:99:99' for datetime
Or
where EndDt < '01/02/2009' but this requires me to increase the date I want to compare by one day.
What is the best way to do this?
Assuming you want all records where EndDt is less than today, this will work:
select
* -- not recommended, should list all columns wanted for the query
from
dbo.MyTable
where
EndDt < dateadd(dd, datediff(dd, 0, getdate()), 0);
Does this help?
November 17, 2008 at 2:04 pm
I also reread your orinial post, and have a change.
Select all records with an end date of yesterday:
select
*
from
dbo.myTable
where
EndDt >= dateadd(dd, datediff(dd, 0, getdate()), -1) and
EndDt < dateadd(dd, datediff(dd, 0, getdate()), 0);
November 17, 2008 at 2:58 pm
Lynn,
Thanks for your replies. You have started me thinking in different direction than I had been. The problem I am trying to solve is one of comparing dates but not the times. I think I will try the following.
Where cast(convert(Varchar(10),EndDt,101) as SmallDateTime) <= '12/01/2008'
Here is why I think this will work. I am a VB.net programmer. 12/01/2008 is the value of a variable I am passing SQLServer. SQL 2008 will implicitly convert 12/01/2008 to 12/01/2008 00:00. Correct?
Then working from the inside out, Convert(10),EndDt,101) will convert the contents of EndDt(smallDatetime) from 12/01/2008 15:31 to a string containing the value, 12/01/2008. The cast will convert the string value, 12/01/2008 to a smalldatetime value of 12/01/2008 00:00. Now any comparison I do, =, >, < will effctively be only comparing the MM/dd/yyyy portion of the date since the times will always be equal at 00:00.
November 17, 2008 at 9:22 pm
mpdillon (11/17/2008)
Lynn,Thanks for your replies. You have started me thinking in different direction than I had been. The problem I am trying to solve is one of comparing dates but not the times. I think I will try the following.
Where cast(convert(Varchar(10),EndDt,101) as SmallDateTime) <= '12/01/2008'
Here is why I think this will work. I am a VB.net programmer. 12/01/2008 is the value of a variable I am passing SQLServer. SQL 2008 will implicitly convert 12/01/2008 to 12/01/2008 00:00. Correct?
Then working from the inside out, Convert(10),EndDt,101) will convert the contents of EndDt(smallDatetime) from 12/01/2008 15:31 to a string containing the value, 12/01/2008. The cast will convert the string value, 12/01/2008 to a smalldatetime value of 12/01/2008 00:00. Now any comparison I do, =, >, < will effctively be only comparing the MM/dd/yyyy portion of the date since the times will always be equal at 00:00.
String conversions of date columns tend to slow things down. Your orignal method works the best... that method would be...
where EndDt < '01/02/2009' but this requires me to increase the date I want to compare by one day.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 17, 2008 at 11:15 pm
mpdillon (11/17/2008)
Lynn,Thanks for your replies. You have started me thinking in different direction than I had been. The problem I am trying to solve is one of comparing dates but not the times. I think I will try the following.
Where cast(convert(Varchar(10),EndDt,101) as SmallDateTime) <= '12/01/2008'
Here is why I think this will work. I am a VB.net programmer. 12/01/2008 is the value of a variable I am passing SQLServer. SQL 2008 will implicitly convert 12/01/2008 to 12/01/2008 00:00. Correct?
Then working from the inside out, Convert(10),EndDt,101) will convert the contents of EndDt(smallDatetime) from 12/01/2008 15:31 to a string containing the value, 12/01/2008. The cast will convert the string value, 12/01/2008 to a smalldatetime value of 12/01/2008 00:00. Now any comparison I do, =, >, < will effctively be only comparing the MM/dd/yyyy portion of the date since the times will always be equal at 00:00.
As Jeff said, doing conversions on the date column will slow things down, and could prevent SQL Server from using any applicable indexes to enhance the query.
If you are passing a single date to a stored procedure that then queries and returns all records for a given time period, say for that day, then the following should work better.
create procedure dbo.SelectOneDayofRecords (
@pDate datetime
)
as
begin
declare @StartDate datetime,
@EndDate datetime;
set @StartDate = @pDate;
set @EndDate = dateadd(dd, 1, @StartDate);
select
* -- just for illistration, actually should specify column names explicitly
from
dbo.MyTable
where
EndDt >= @StartDate and
EndDt < @EndDate;
return (0)
end
If you call the stored proc as follows:
exec dbo.SelectOneDayofRecords '12/01/2008'
It will return all records where the date is 12/01/2008, regardless of the time portion.
November 18, 2008 at 5:03 am
Jeff, I didn't know that the string conversions would slow things down. Thanks for that piece of information. I had used the increase the day by one trick previously. But this time I wanted to find a more intuitive method. Some(OK most) of my queries are difficult to read. Having to interpret dates makes them even more so when I am troubleshooting.
Lynn, You have lots of good ideas. I just lack the skill set to implement them. I know of stored procedures but I have no idea how to implement them from my Visual Basic program. I use ADO.Net. Every interaction I have with SQL server is through a SQLClient.SQLConnection and a SQLClient.SQLCommand. It is the SQLCommand object that receives my query strings.
Disasters often start this way but I not going to implement your suggestions. But I really do appreciate your comments. I will most certainly use them in the future.
In this instance, I am writting an Employee Scheduling application. Each company will have it's own set of data tables. The average user should be about 100 employees. The queries come into play during the scheduling process. The scheduling process runs against temporary tables. These tables have a record count of about 700(7 days x 100 employees).
I have made a conscious effort during this project to pass as much work to the SQL server as possible thereby using less loops and calls in my VB code. As a result, some of my queries are quite complex and long. There are a couple that exceed two pages in MS Word(Lynn - I never use * either). Yet during my testing, none of these queries take very long(to my eye the time to return the data is imperceptable so I will call it less than a second.).
When I was designing the project I included the address of the SQL server in everyone's login record. Should I ever notice a decrease in performance it will be a fairly trivial matter to add additional SQL servers. Actually, I hope I sell enough of this that adding servers becomes a "problem". At that time I will get a crash course in clustering vs adding seperate servers.
Anyway, thanks for your insights.
pat
November 18, 2008 at 5:58 am
mpdillon (11/18/2008)
But this time I wanted to find a more intuitive method. Some(OK most) of my queries are difficult to read. Having to interpret dates makes them even more so when I am troubleshooting.
Heh... that's precisely why MS included the ability to comment your code...
Disasters often start this way...
That's exactly correct... and you're headed for one right now... making a correction to one of your original pieces of code...
Where EndDt <= '01/01/2009 23:59' for smalldatetime
and
Where EndDt <='01/01/2009 13:59:59.999' for datetime
The first piece misses almost a whole minute of the day. The second piece will actually round up to '01/02/2009' because time is based on 3.33 milliseconds.
In this instance, I am writting an Employee Scheduling application. Each company will have it's own set of data tables. The average user should be about 100 employees. The queries come into play during the scheduling process. The scheduling process runs against temporary tables. These tables have a record count of about 700(7 days x 100 employees).
Yep... and if you make it big time (I trully hope you do) and you end up with a company that has 50,000 or 100,000 employees, you'll need to scramble to make it performant at the newly required scale.
I have made a conscious effort during this project to pass as much work to the SQL server as possible thereby using less loops and calls in my VB code. As a result, some of my queries are quite complex and long. There are a couple that exceed two pages in MS Word(Lynn - I never use * either). Yet during my testing, none of these queries take very long(to my eye the time to return the data is imperceptable so I will call it less than a second.).
Again, that's only because you only have a small amount of data. If you win big, it may take much, much more time.
When I was designing the project I included the address of the SQL server in everyone's login record. Should I ever notice a decrease in performance it will be a fairly trivial matter to add additional SQL servers. Actually, I hope I sell enough of this that adding servers becomes a "problem". At that time I will get a crash course in clustering vs adding seperate servers.
Lot's of companies have made the same mistake. I've seen companies spend a lot of money on hardware and upgrading SQL Server only to find out that it didn't help performance because the code is the problem, not the hardware.
Anyway, thanks for your insights.
My recommendation is to study how to make stored procedures and how to make bullet-proof dynamic SQL that will withstand all forms of SQL Injection attacks. I wouldn't write another lick of code without achieving a perfect understanding of both. Yep... it'll take some research and some practice. But, it WILL pay off, big time... and that's what you're aiming for... the "Big Time". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2008 at 8:02 am
Going in a different direction, away from your queries, to indexes. How are you indexing your tables? Will you have a primary key on each table? Will you have a clustered index on each table? Will the primary key be the clustered index? Will you make use of covered indexes (included columns)? How will you decide which index to make the clustered index on a table if it isn't the primary key?
Just a few more things to think about as you design and build your application.
You really should heed the advice you will receive by asking questions on SSC. Many of us have gone through the fire and have seen the things that work and those that don't. Use this site to help you build a better application than you thought you could.
November 19, 2008 at 3:40 am
hi mpdillon ,
I think u don't want to compare time value with the date present in the column. For this problem , i have one function that will remove the time part of the column and than compare it.
ALTER FUNCTION [dbo].[ufn_GetDateOnly] (@inputdatetime smalldatetime)
RETURNS smalldatetime AS
BEGIN
RETURN CAST(CAST(MONTH(@inputdatetime) AS VARCHAR(2)) + '/' +
CAST(DAY(@inputdatetime) AS VARCHAR(2)) + '/' +
CAST(YEAR(@inputdatetime) AS VARCHAR(4)) AS smalldatetime)
END
Now u can write ur query as :
Where dbo.ufn_GetDateOnly(EndDt) <= '01/01/2009'
Thanks
Shailesh
November 19, 2008 at 7:09 am
shailesh (11/19/2008)
Where dbo.ufn_GetDateOnly(EndDt) <= '01/01/2009'
And doing this can result in your query not taking advantage of indexing on a table. If your tables are small, you may not notice, but this may not scale well to multimillion row tables.
November 19, 2008 at 3:03 pm
A friend suggested the following. It seems to be cleaner than mine.
Where cast(varchar(8),EndDt,112) <= Format('01/02/2009',"yyyyMMdd") Sorry for mixing T-SQL and VB.
However, both Jeff and Lynn bring up the issue of using the indexes. Could any of the methods suggested in this post utilize indexes?
PS - I thought this was a simple question. I am surpised by the quantity and quality of the responses. Thanks. I find it surprising the issue doesn't seem to have been addressed by T-SQL directly. I would think this is a pretty common occurance.
November 19, 2008 at 3:26 pm
Let us drop back 10 yards and punt.
First, answer the following question, what are you attempting to do with this particular query? Are you trying to return all records entered during a specific period of time? Is this specific period of time fixed or variable? What?
The more information the better.
Aside, you really want to try and stay away from using functions on columns on the left side of an arguement such as this:
WHERE SomeFunction(SomeColumn) <= SomeValue
The term you will sometimes hear is SARGable. By using a function on the left, you may prevent SQL Server from making use of appropriate indexes and the result will be a table/clustered index scan (clustered index scan = table scan).
November 19, 2008 at 7:49 pm
Lynn Pettis (11/19/2008)
shailesh (11/19/2008)
Where dbo.ufn_GetDateOnly(EndDt) <= '01/01/2009'
And doing this can result in your query not taking advantage of indexing on a table. If your tables are small, you may not notice, but this may not scale well to multimillion row tables.
... and even if it did use an index, it would still be slow because of all the trips through "string land".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2008 at 7:54 pm
mpdillon (11/19/2008)
A friend suggested the following. It seems to be cleaner than mine.Where cast(varchar(8),EndDt,112) <= Format('01/02/2009',"yyyyMMdd") Sorry for mixing T-SQL and VB.
However, both Jeff and Lynn bring up the issue of using the indexes. Could any of the methods suggested in this post utilize indexes?
PS - I thought this was a simple question. I am surpised by the quantity and quality of the responses. Thanks. I find it surprising the issue doesn't seem to have been addressed by T-SQL directly. I would think this is a pretty common occurance.
Again, the proper way to do this is as you originally did in your 3rd example with adding 1 day... it's the only way an index stands a chance of being used and it's one of the very few ways that won't either missing that last moments of the final day nor include the first moment of the next but not included day.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply