November 9, 2009 at 8:01 am
hi
i want to get values between two dates. here is the example i did.
ex:
table structure:
CREATE TABLE a
(
col [datetime] NULL
) ON [PRIMARY]
and inserted data into the table.
select * from a;
2008-06-01 00:00:00.000
2008-06-02 00:00:00.000
2008-06-03 00:00:00.000
2008-06-04 00:00:00.000
2008-06-05 00:00:00.000
2008-06-06 00:00:00.000
2008-10-06 00:00:00.000
2008-10-01 00:00:00.000
2008-10-02 00:00:00.000
2008-10-03 00:00:00.000
2008-10-04 00:00:00.000
2008-10-05 00:00:00.000
2008-10-06 00:00:00.000
2008-10-07 00:00:00.000
14 records found.......
i am having problem in retrieving the data between two dates.
ex1:
select * from a where CONVERT(varchar,col,101) between '05/01/2000' and '10/20/2000'
14 records found....
ex2:
select * from a where CONVERT(varchar,col,101) between '05/01/2007' and '10/20/2007'
14 records found
but i have inserted only 2008 year records.and there are no date values based on other years except 2008..
why i am getting values based on other years also.
Thanks
Rock..
November 9, 2009 at 8:41 am
I don't think you should be converting to varchar. Just try where colName between 'mm/dd/yy' and 'mm/dd/yy'
November 9, 2009 at 9:38 am
Absolutely. And taking the CONVERT(...) out of your WHERE clause will also improve performance of your query too.
November 9, 2009 at 2:50 pm
Yeah, definitely do not do the convert to varchar as that totally changes things, let SQL Server do a data compare. Also, be careful with BETWEEN. Check out what happens when you do this:
DECLARE @temp TABLE (col1 DATETIME);
INSERT INTO @temp (
col1
)
SELECT
'20090101'
UNION ALL
/* note the 1 second after midnight */
SELECT
'20090131 00:00:01';
/* now give me all the rows in January */
SELECT
*
FROM
@temp
WHERE
col1 BETWEEN '20090101' AND '20090131'
/* Now this returns what I really want */
SELECT
*
FROM
@temp
WHERE
col1 >= '20090101' AND
col1 < '20090201'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 2:56 pm
by standard our app always use full dates such as
select * from table where date between '2009-01-01 00:00:00' and '2009-01-31 23:59:59'
as others said, try droping the convert and let sql server handles the conversion
--
Thiago Dantas
@DantHimself
November 9, 2009 at 3:00 pm
Not to nitpick, but what if there are milliseconds on the date? You are much safer using >= and < when dealing with dates.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 11:07 pm
hi Jack Corbett
you are correct.
select * from a where col>='2008-01-01'and col<'2008-10-10'
it is working fine. getting values as per the requirement.
can you tell me the reason,why it is not working properly with convert of datetime to a mmddyyyy, is there any specific reason for that?
But i have a problem, i try to keep it in dynamic query,i am getting error.
alter proc test
(@t datetime
,@t1 datetime)
as
begin
declare @sql varchar(4000)
set @sql= 'select * from a where (col>='''+ @t +''' and col<'''+ @t1+''')'
print @sql
execute(@sql)
end
execute test '2007-01-01','2007-10-10'
error:
Msg 241, Level 16, State 1, Procedure test, Line 8
Conversion failed when converting date and/or time from character string.
November 9, 2009 at 11:22 pm
i resolved the error.
can you tell me the reason,why it is not working properly with convert of datetime to a mmddyyyy, is there any specific reason for that?
Thanks
Rock...
November 10, 2009 at 5:38 am
When you convert a date to varchar it is doing character conversion which is different than data conversion. You would need to be sure that the date strings are formatted yyyymmdd in all cases or else the comparison will not be accurate. Using the appropriate data type, in this case, datetime or smalldatetime helps avoid problems like this.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 10, 2009 at 9:52 am
Thank you very much Jack Corbett.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply