Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 [RESOLVED] Compare dates regardless of time?

Author  Topic 

DTFan
Yak Posting Veteran

52 Posts
Posted - 2005-05-20 : 12:34:55
I have what I hope is a simple question. I've got a MSDE table with the following fields:

tblCustomer
custID (int)
custFName (varchar 30)
custLName (varchar 30)
custEmail (varchar 75)
custJDate (smalldatetime)
custPhone (biginit)

So, I'm trying to write a query that will return all Customers who joined on a certain date (regardless of the time field for that date). When the user signs up, their information is entered into this table and custJDate is set from a variable (I'm using VB.NET) which is Now(). So, you might have 3 users with todays date but they are not showing up when I run this query:


SELECT custID, custFName + ' ' + custLName AS custFullName, 
     custEmail, custJDate, custPhone 
FROM tblCustomer
WHERE custJDate = '5/20/2005'


The date (that you see in the single quotes) is actually a variable (dtDateToCheck) of datatype DateTime which the user selects from a Calendar.

The strange thing is that if I set the tblCustomer.custJDate to "5/20/2005 12:00:00 AM", then the query will return the record. But if the time in that field is anything else, the query doesn't return that record.

So it seems to me it is a time issue. But I don't know how to work around that.

Any and all help will be GREATLY appreciated

DTFan
Ever-hopeful programmer-in-training

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-05-20 : 12:55:38
...
Where convert(datetime,convert(varchar,custJDate,101)) = '5/20/2005'

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

DTFan
Yak Posting Veteran

52 Posts
Posted - 2005-05-20 : 13:23:28
quote:
Originally posted by Seventhnight

...
Where convert(datetime,convert(varchar,custJDate,101)) = '5/20/2005'


That did it. I know this is a stupid question but if "dtDateToCheck" is stored as a DateTime, then why would I have to convert the smalldatetime to a varchar to compare the two? Unless it's because the dtDateToCheck is only storing the date (and not the time)?

Regardless, THANK YOU for the answer (it's been driving me kinda nuts).



DTFan
Ever-hopeful programmer-in-training
Go to Top of Page

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts
Posted - 2005-05-20 : 13:32:49
it is being stored as a date and a time, but the conversion 'convert(varchar,dtDateToCheck,101)' basically strips the time to leave midnight. converting back to a date keeps a date comparison rather than a text comparison.

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts
Posted - 2005-05-20 : 13:39:27
And when you populate a datetime field, if you only supply the date portion, it defaults to midnight. So, in your VB application, instead of using Now() which has both date and time, if you used Date(), you would put only dates with 00:00:00 time into your table.

---------------------------
EmeraldCityDomains.com
Go to Top of Page

DTFan
Yak Posting Veteran

52 Posts
Posted - 2005-05-20 : 14:08:03
Thanks for the help and for both answers. I'm going to go ahead and change the Now() to Date(). The conversion thing is something feel quite confident in saying I never would have figured out.

I'm so glad this was answered before the weekend or my weekend would have been spent poring through SQL books. That's not necessarily a bad thing but being that I've been doing that all week I'm kinda looking forward to a break.

Thanks again. This site rocks.



DTFan
Ever-hopeful programmer-in-training
Go to Top of Page

rkasse
Starting Member

14 Posts
Posted - 2005-05-21 : 17:52:01
quote:
Originally posted by Seventhnight

...
Where convert(datetime,convert(varchar,custJDate,101)) = '5/20/2005'

Using the convert function on the datetime column custJDate will force a table scan. Try something like this which will allow the index on custJDate to be used:

...Where custJdate >= Dateadd(d, datediff(d, 0, @DateToCheck), 0)
AND custJdate < Dateadd(d, datediff(d, 0, @DateToCheck), 1) ...

For a full explanation and a good article on working with sql server dates see:
http://www.sql-server-performance.com/fk_datetime.asp
Go to Top of Page

praveenbattula
Starting Member

4 Posts
Posted - 2010-06-15 : 23:07:32
I believe this could be the efficient way of doing it, instead of using Convert method.
[url]http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html[/url]

Please let me know, what do you think...

thanks

Rare Solutions
http://praveenbattula.blogspot.com
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-06-16 : 03:07:45
quote:
Originally posted by praveenbattula

I believe this could be the efficient way of doing it, instead of using Convert method.
[url]http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html[/url]

Please let me know, what do you think...

thanks

Rare Solutions
http://praveenbattula.blogspot.com


SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);

can be effeciently converted to

SELECT * FROM [Employee]
WHERE
DateSent>= DATEADD(dd,(DATEDIFF(dd,0,@Date)),0) and
DateSent< DATEADD(dd,(DATEDIFF(dd,0,@Date))+1,0)




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kuklei
Starting Member

3 Posts
Posted - 2010-07-05 : 18:52:27
quote:
Originally posted by madhivanan

quote:
Originally posted by praveenbattula

I believe this could be the efficient way of doing it, instead of using Convert method.
[url]http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html[/url]

Please let me know, what do you think...

thanks

Rare Solutions
http://praveenbattula.blogspot.com


SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);

can be effeciently converted to

SELECT * FROM [Employee]
WHERE
DateSent>= DATEADD(dd,(DATEDIFF(dd,0,@Date)),0) and
DateSent< DATEADD(dd,(DATEDIFF(dd,0,@Date))+1,0)




Madhivanan

Failing to plan is Planning to fail


Madhivanan, why do you think your method is more efficient than the one described on the link from praveenbattula. As a matter of fact the one on the link has only one = operator while you are using the between like expression which will force more calcs on the server.

praveenbattula
Your link is exactly what I needed. Thanks
Kleidi
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts
Posted - 2010-07-06 : 01:39:06
How about
SELECT * FROM [Employee]
WHERE datediff(d,dateSent,@date)=0
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-07-06 : 02:18:54
quote:
Originally posted by kuklei

quote:
Originally posted by madhivanan

quote:
Originally posted by praveenbattula

I believe this could be the efficient way of doing it, instead of using Convert method.
[url]http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html[/url]

Please let me know, what do you think...

thanks

Rare Solutions
http://praveenbattula.blogspot.com


SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);

can be effeciently converted to

SELECT * FROM [Employee]
WHERE
DateSent>= DATEADD(dd,(DATEDIFF(dd,0,@Date)),0) and
DateSent< DATEADD(dd,(DATEDIFF(dd,0,@Date))+1,0)




Madhivanan

Failing to plan is Planning to fail


Madhivanan, why do you think your method is more efficient than the one described on the link from praveenbattula. As a matter of fact the one on the link has only one = operator while you are using the between like expression which will force more calcs on the server.

praveenbattula
Your link is exactly what I needed. Thanks
Kleidi

If the date column is idexed, praveenbattula's query will not make use of it thus causing a table scan. My method uses functions on the date variables so index can be used

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-07-06 : 02:19:49
quote:
Originally posted by stepson

How about
SELECT * FROM [Employee]
WHERE datediff(d,dateSent,@date)=0

It is simple but will not make use of index if it is defined on the date column

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

kuklei
Starting Member

3 Posts
Posted - 2010-07-08 : 20:26:55
Thanks madhivanan for the heads up. However, praveen's link uses the same functions that your solution uses. Why is it that one forces a table scan why the other (yours) doesn't?

This is praveen link
SELECT * FROM [Employee] WHERE DATEADD(dd,(DATEDIFF(dd,0,DateSent)),0) = DATEADD(dd,(DATEDIFF(dd,0,@Date)),0);
Go to Top of Page

kuklei
Starting Member

3 Posts
Posted - 2010-07-08 : 20:28:46
quote:
Originally posted by madhivanan

quote:
Originally posted by stepson

How about
SELECT * FROM [Employee]
WHERE datediff(d,dateSent,@date)=0

It is simple but will not make use of index if it is defined on the date column

Madhivanan

Failing to plan is Planning to fail


Very simple and elegant stepson. I wonder why this one does not make use of indexes?

Thanks for the feedback, guys
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-07-08 : 21:29:40
Madhivanan's solution is more efficient and can use an index because the functions are to one side of the predicate while the column is by itself on the other.

Elegant and simple doesn't mean efficient. Go with what can use an index, which is madhivanan's solution. The other solutions can't use an index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

ahmeds08
Aged Yak Warrior

737 Posts
Posted - 2010-11-14 : 23:55:38
u can compare by using the convert function.the date format which you need to take is 101.becoz this format returns only the datepart.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2010-11-15 : 05:06:30
quote:
Originally posted by ahmeds08

u can compare by using the convert function.the date format which you need to take is 101.becoz this format returns only the datepart.

Did you read previous replies?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site