March 9, 2009 at 2:27 am
Somthing strange is happening?!
I have restored a SQL 2000 DB on to a new SQL 2005 DB.
When I run:
select top 100 * from tbldeals
order by trade_date desc
In get the results i want, i.e. data dated '2009-03-06 00:00:00'
But when I run:
select * from tbldeals
where trade_date = '2009-03-06 00:00:00'
I get no results!!!!???
Is this somthing to do with the way i have restored the DB?
March 9, 2009 at 6:48 am
March 9, 2009 at 7:04 am
A Little Help Please (3/9/2009)
where trade_date = '2009-03-06 00:00:00'Assuming trade_date is a datetime column, what do you think the result of the implicit conversion of the string '2009-03-06 00:00:00' to datetime will be, March or June? Try the following:
set dateformat dmy
select * from tbldeals
where trade_date = '2009-03-06 00:00:00'
set dateformat mdy
select * from tbldeals
where trade_date = '2009-03-06 00:00:00'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2009 at 7:23 am
You may want to take a look at this article from BOL, http://msdn.microsoft.com/en-us/library/ms191307(SQL.90).aspx
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
March 9, 2009 at 7:29 am
Thanks all for your input.
1. No error messages but no results retuned...
2. The column is set as Trade_date (SmalldateTime,Null)
March 9, 2009 at 7:59 am
Have you tried the following yet?
Select * from tbldeals
where trade_date = '03/06/2009';
Depending on your database collation, this might make all the difference.
March 9, 2009 at 8:46 am
This should work let us know:
SELECT * FROM TBLDEALS
WHERE CONVERT(VARCHAR(20),trade_date, 120) = '2009-03-06 00:00:00'
March 9, 2009 at 8:52 am
This did not work
collation is: SQL_Latin)General_CP1_CI_AS
March 9, 2009 at 8:58 am
Dugi (3/9/2009)
This should work let us know:
SELECT * FROM TBLDEALS
WHERE CONVERT(VARCHAR(20),trade_date, 120) = '2009-03-06 00:00:00'
It might work, Dugi, but it's not going to be quick! Far better to convert one literal string '2009-03-06 00:00:00' into a variable of the right datatype and match that to a column, than convert a column (which might be conveniently indexed) into another datatype to match to a value.
With no other restrictions in the where clause, if this table has a million rows, then this CONVERT(VARCHAR(20),trade_date, 120) will have to be performed a million times - even if no rows match, or one, or two.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2009 at 9:06 am
A Little Help Please (3/9/2009)
Somthing strange is happening?!I have restored a SQL 2000 DB on to a new SQL 2005 DB.
When I run:
select top 100 * from tbldealsorder by trade_date desc
In get the results i want, i.e. data dated '2009-03-06 00:00:00'
But when I run:
select * from tbldealswhere trade_date = '2009-03-06 00:00:00'
I get no results!!!!???
Is this somthing to do with the way i have restored the DB?
Try this in your WHERE clause:
trade_date >= '2009-03-06 00:00:00.000' and trade_date < '2009-03-07 00:00:00.000'
March 9, 2009 at 10:20 am
If you aren't getting the values you expect, then something is wrong. Either your table / column is case sensitive despite the fact that your DB is case insensitive or the column in question isn't really a smalldatetime or you have no timestamps that equal midnight.
Try a >= instead of an equal in your WHERE clause and order ASC just to see what happens. Don't do a between or add a <= to the statement. I'm just curious to see what happens when you check that date at midnight if you get any records greater than or equal to.
BTW, collations can be applied differently on a table level than on a database level. So, if trade_date is Trade_Date or Trade_date or trade_Date, you won't get results back because it can't find the column you're referring to. Of course, I'd expect an error message instead of a NULL result set, but it's something to double-check anyway.
March 9, 2009 at 10:29 am
If trade_date is a datetime column, then collation is irrelevant, and the character constants will be converted to a datetime value before the comparison.
March 9, 2009 at 10:43 am
Lynn,
I'm not talking values. I'm talking names. If the collation is not the same as the one he listed, then the name "trade_date" might not be the column name.
But again, I'd expect an error rather than a null record set if that were the case.
March 9, 2009 at 9:07 pm
A Little Help Please (3/9/2009)
Somthing strange is happening?!I have restored a SQL 2000 DB on to a new SQL 2005 DB.
When I run:
select top 100 * from tbldealsorder by trade_date desc
In get the results i want, i.e. data dated '2009-03-06 00:00:00'
But when I run:
select * from tbldealswhere trade_date = '2009-03-06 00:00:00'
I get no results!!!!???
Is this somthing to do with the way i have restored the DB?
Print the TOP 10 * results you're getting so folks can see what you see.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply