SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 [RESOLVED] Compare dates regardless of time?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DTFan
Yak Posting Veteran

USA
52 Posts

Posted - 05/20/2005 :  12:34:55  Show Profile  Reply with Quote
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

Edited by - DTFan on 05/20/2005 13:26:36

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/20/2005 :  12:55:38  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
...
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

USA
52 Posts

Posted - 05/20/2005 :  13:23:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 05/20/2005 :  13:32:49  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
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

USA
3246 Posts

Posted - 05/20/2005 :  13:39:27  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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

USA
52 Posts

Posted - 05/20/2005 :  14:08:03  Show Profile  Reply with Quote
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 - 05/21/2005 :  17:52:01  Show Profile  Reply with Quote
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

India
4 Posts

Posted - 06/15/2010 :  23:07:32  Show Profile  Reply with Quote
I believe this could be the efficient way of doing it, instead of using Convert method.
http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html

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

thanks

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

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 06/16/2010 :  03:07:45  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by praveenbattula

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

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

Albania
3 Posts

Posted - 07/05/2010 :  18:52:27  Show Profile  Reply with Quote
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.
http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html

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
Constraint Violating Yak Guru

Romania
431 Posts

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

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 07/06/2010 :  02:18:54  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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.
http://praveenbattula.blogspot.com/2010/06/compare-date-only-in-datetime-field-in.html

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

India
22760 Posts

Posted - 07/06/2010 :  02:19:49  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Albania
3 Posts

Posted - 07/08/2010 :  20:26:55  Show Profile  Reply with Quote
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

Albania
3 Posts

Posted - 07/08/2010 :  20:28:46  Show Profile  Reply with Quote
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

USA
36985 Posts

Posted - 07/08/2010 :  21:29:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

India
649 Posts

Posted - 11/14/2010 :  23:55:38  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
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

India
22760 Posts

Posted - 11/15/2010 :  05:06:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000