December 11, 2011 at 6:32 pm
I'm trying to query a databse that stores records with date/time stamp format - I need to return results for the entire day.......
Example Order Date is stored as follows:
2011-11-30 17:53:00.000
If I use...........select * from Orders where orderdate = '2011-11-30' or if I try a like statement as follows:
select * from Orders where orderdate like '%2011-11-30%'
I don't get my desired results - I must need to use CAST, Convert or Trim (guessing)???
Thanks in advance - I didn't see anything in BOL that would give me immediate assistance......lots of verbage about the different formats and converting the data - I don't want to convert - just query........
December 11, 2011 at 6:48 pm
I think I found my answer on this website (should have searched here before posting...........
I tried this............
select * from orders where cast(orderdate as date) = '2011-11-30'
It seems to work - still checking my results........
Anybody agree?
December 12, 2011 at 12:55 am
Please provide the DDL for your table so we can give you proper advise. Follow the link to Jeff Moden's article on posting etiquette in my footer text if you don't know what this means.
Guessing that the timestamp value is stored in a column with a type derived from datetime, I would suggest to avoid using the type-casts as these will make SQL server not use any indices that may be available on the table, ergo your queries will become slow fast when the table grows.
Instead use >= and < to specify a date range. This enables SQL server to use any indices if available. Like this:
select *
from dbo.Orders
where orderdate >= {d '2011-12-11'}
and orderdate < {d '2011-12-12'}
December 12, 2011 at 2:05 am
R.P.Rozema provided the solution.
I just want to point to Some common date routines by Lynn Pettis
and
my rules of thumb for date/datetime related stuff http://qa.sqlservercentral.com/Forums/FindPost1219915.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 12, 2011 at 4:09 am
Thanks everyone for response.
For the record both of these queries worked and returned exact same results:
select * from Orders where cast(orderdate as date) = '2011-11-30'
select *
from dbo.Orders
where orderdate >= {d '2011-11-30'}
and orderdate < {d '2011-12-01'}
RP - thank you for your response including tips on providing actual data, queries attempted, expected results, etc. I will try to provide actual data next time.
Regards,
BT
December 12, 2011 at 5:53 am
Bron Tamulis (12/12/2011)
Thanks everyone for response.For the record both of these queries worked and returned exact same results:
select * from Orders where cast(orderdate as date) = '2011-11-30'
select *
from dbo.Orders
where orderdate >= {d '2011-11-30'}
and orderdate < {d '2011-12-01'}
RP - thank you for your response including tips on providing actual data, queries attempted, expected results, etc. I will try to provide actual data next time.
Regards,
BT
Actually, they DON'T provide the same results or at least not in the same amount of time... the query that uses CAST will be significantly slower and use significantly more resources because it will never be able to use an INDEX SEEK.
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply