July 6, 2008 at 3:15 pm
Hi All,
I am new to this so need some expert guidance. In one of my datetime field data is stored in the format
yyyy-mm-dd hh:min:sec.
I am trying to retrieve the records between start date and end date from the user and date format is mm/dd/yyyy.
If i write the following query
1. select field name from tab1 where field name is >= '07/01/2008' and <= '07/03/2008' it doesnot return all records which took place on '07/03/2008' but if i write
2.select field name from tab1 where field name is >= '07/01/2008' and <= '07/04/2008' then it returns all records..
Can anyone suggest how to retrieve all records between two dates from query 1.
Regards,
Pradeep
July 6, 2008 at 6:39 pm
pradeepkk2003 (7/6/2008)
Hi All,I am new to this so need some expert guidance. In one of my datetime field data is stored in the format
yyyy-mm-dd hh:min:sec.
I am trying to retrieve the records between start date and end date from the user and date format is mm/dd/yyyy.
If i write the following query
1. select field name from tab1 where field name is >= '07/01/2008' and <= '07/03/2008' it doesnot return all records which took place on '07/03/2008' but if i write
2.select field name from tab1 where field name is >= '07/01/2008' and <= '07/04/2008' then it returns all records..
Can anyone suggest how to retrieve all records between two dates from query 1.
Regards,
Pradeep
First, verify whether or not your column is actually stored as a datetime or a varchar\char. If it is defined as a char\varchar - you definitely need to look at changing it to a datetime.
Now, as to your problem - what is happening is that you are experience implicit data type conversion. What that means is that when you specify '07/01/2008' - SQL Server is implicitly converting that to a datetime data type that is represented as '2008-07-01T00:00:00.000', or July 1st at midnight. When you use '07/03/2008' it is being implicitly converted to '2008-07-03T00:00:00.000'.
BTW - this shows us that the column being compared is defined as datetime since you are getting the implicit conversion to datetime to match the column.
The question for you is, is '2008-07-03T13:58:58.000' less than '2008-07-03T00:00:00.000'?
As a general practice, you should always use the next day at midnight for date ranges. For example, in your query it should be:
Select column(s)
From table
Where datecolumn >= '20080701'
And datecolumn < '20080704';
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2008 at 1:33 am
Hi Jeff,
Thanks for your reply. The start date and end date are selected by the user, so what if he selects 1st July and 3rd July , will your query fetches all results till 3rd July midnight or 2nd July midnight , do i hv to do + 1 for my enddate.
July 7, 2008 at 8:13 am
pradeepkk2003 (7/7/2008)
Hi Jeff,Thanks for your reply. The start date and end date are selected by the user, so what if he selects 1st July and 3rd July , will your query fetches all results till 3rd July midnight or 2nd July midnight , do i hv to do + 1 for my enddate.
If you specify < '20080703' then you will get all records until midnight July 2nd.
If you want your query to include results from the last day the user selects, then you need to add one to the selected date.
July 7, 2008 at 2:06 pm
Hello,
When requesting a date range, one must remember the time part of the date.
So whenever requesting a date range use code like this :
select *
from tablexyz
where convert(varchar(10), start_date, 121) between '2008-01-01' and '2008-02-25'
This convert funtion call will remove the time part and the string format 121 matches the suggested string ranges.
Hope this helps.
Regards,
Terry
July 7, 2008 at 3:06 pm
tbeadle (7/7/2008)
Hello,When requesting a date range, one must remember the time part of the date.
So whenever requesting a date range use code like this :
select *
from tablexyz
where convert(varchar(10), start_date, 121) between '2008-01-01' and '2008-02-25'
This convert funtion call will remove the time part and the string format 121 matches the suggested string ranges.
Hope this helps.
Regards,
Terry
Terry, although this will work it is not recommended. Using CONVERT on the column will eliminate the usage of any indexes on the column being converted (e.g. start_date). And, because BETWEEN is inclusive, the only reliable way to use between with dates is to do the following:
WHERE start_date BETWEEN '2008-01-01T00:00:00.000' AND '2008-02-25T23:59:59.997'
The above will require that you convert the input dates (if the dates are passed in as parameters to a procedure) so that you strip the time for both date parameters, and then add back into the end date parameter the time as above.
I prefer the method of stripping the time from both parameters, adding 1 to the end date parameter and using:
WHERE start_date >= @begin_date
AND start_date < @end_date
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 7, 2008 at 11:07 pm
You can do following if your start_date is of character type and you have problems comparing the date columns:
select * from tablexyz
where convert(datetime,convert(varchar(10), getdate(), 101)) between '01/01/2008' and '02/25/2008'
--date format is mm/dd/yyyy
July 8, 2008 at 3:53 am
Hi Pradeep,
U can try this
where field name >= '07/01/2008' and dateadd(dd,-1,field name ) < '07/03/2008'
regards,
Balamurugan G
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply