?
Solved

SMALLDATETIME Question

Posted on 2002-05-01
16
Medium Priority
?
631 Views
Last Modified: 2012-05-04
I have the following WHERE:

WHERE      liabhist.logtime = '4/23/02'


liabhist.logtime is a SMALLDATETIME and the WHERE does not work.  How do code the where so it will pick up all records from 4/23/02?
0
Comment
Question by:jasonboetcher
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 5

Accepted Solution

by:
spcmnspff earned 200 total points
ID: 6983369
Do this instead:

WHERE Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) = '4/23/02'


This causes every logtime to look like '4/23/02 12:00:00 AM'  Which is what the other side oh the = sign looks like after it is converted too . . ..

0
 
LVL 6

Expert Comment

by:curtis591
ID: 6983412
WHERE      liabhist.logtime >= '4/23/02' and liabhist.logtime < '4/24/02'
0
 

Author Comment

by:jasonboetcher
ID: 6983425
Which of these suggestions is more efficient?
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 6

Expert Comment

by:curtis591
ID: 6983458
WHERE Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) = '4/23/02' seems that it would be quite hard to read and by looking at it it seems that it would be doing a lot of calculations.  I just tried the queries in query analyzer on our invoice table about 1.5 million records I ran both queries together my way the other way.

My way took .07% of the total time to execute the other query with the convert took 99.93% of the time.  
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6983460
testing for equality (WHERE Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) = '4/23/02') is faster then the range method (WHERE      liabhist.logtime >= '4/23/02' and liabhist.logtime < '4/24/02' )
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 6983461
it;s about the same.  Mine will cause slightly more CPU but less reads.  If your really curious run each query in Query analyzer like this

set statistics io on
set statistics time on

. . . .
WHERE      liabhist.logtime >= '4/23/02' and liabhist.logtime < '4/24/02'

Then run the other one in a different window . . .


set statistics io on
set statistics time on

. . . .
WHERE      liabhist.logtime >= '4/23/02' and WHERE Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) = '4/23/02'


And compare the reads and over all exec time . . . this will tell you for sure . . .
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 6983482
No need to convert to char before converting to date:

Convert(Datetime, liabhist.logtime,101) = '4/23/02'
0
 
LVL 6

Expert Comment

by:curtis591
ID: 6983488
It is going to depend on how you have your table setup.  I am hitting my table on a clustered index.  I am returning 1300 records.  I would say the more records you have to go through the worse the Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) query is going to be because it is going to calculate that on every field.
0
 
LVL 6

Expert Comment

by:curtis591
ID: 6983498
Without the extra convert that evens the execution time out to the same but the query plan has become more complicated.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 6983506
I agree.  The table set up is the key.  This all depends on what kind of indexes are on the table and their integrity/cardinality.  But in general the less reads the better.  It's a good idea to sacrifice CPU for reads.  CPU happens alot faster and doesn't strain your disk subsystem.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6983536
It's my understanding of SQL Server optimizer that it won't use an index for a comparison such as:

WHERE Convert(Datetime,Convert(VarChar,liabhist.logtime,101)) = '4/23/02'

but it will use one for:

WHERE liabhist.logtime >= ... AND < ...

On a large table, that could make a HUGE difference.  I don't think the CONVERT is that much overhead (it's just a little CPU anyway), but not using an index could be a killer.

By the way, if you do decide to run comparisons, be sure to do a:
DBCC DROPCLEANBUFFERS
to empty all buffers between runs.  Otherwise the second run should always be faster (the data needed has already been read into the buffer).
0
 
LVL 6

Expert Comment

by:curtis591
ID: 6983573
I can run those queries any way and it will return the same results both ways.  I take the extra covert out and they are even I put it back in and is quite a bit slower. There is a definate balance between CPU and reads but the query took about 6 seconds for me to execute.  If I have a choice between 6 second and .5 second response.  You have to go with the .5 second.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 6983655
Well, I took my own advice and tested the IO and Exec times.  My query sucked wind compared to Curtis591's. there were about twice the reads.  I think ScottPletcher is right.  It doesn't seem to be using the index.  I even added in a with index table hint, but no change.  Interesting topic . . . =)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6983659
You might want to do a SHOWPLAN and see if the CONVERT version is doing some type of table/index scan versus the other method doing some type of index seek.  It would be interesting to know.
0
 
LVL 5

Expert Comment

by:spcmnspff
ID: 6983688
It's doing an idex scan rather than a seek.  There was also a bookmark lookup that can be eliminated if I only had the indexed field in the select list (rather than Select *).
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 6983741
Thanks for checking on that.  It's interesting.  It's also about what I expected.  

And obviously the larger the table, the more significant a SCAN vs. SEEK will be.  On a large table it's probably worth some extra code to be able to do the WHERE on the original column rather than a formula/derivation based on that column; do the manipulation on the local variables to be compared against the column, not the original column itself.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question