SQL Query not running...

Question

Post reply

SQL Query not running...

A Little Help Please

SSCarpal Tunnel

Points: 4764
More actions
March 9, 2009 at 2:27 am

#218963

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?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

What is you error msg!?

As I can see in the WHERE clause you have the problem with date format!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 4

Thanks all for your input.

1. No error messages but no results retuned...

2. The column is set as Trade_date (SmalldateTime,Null)

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 5

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Dugi SSCoach Points: 17998 More actions · Answer 6

This should work let us know:

SELECT * FROM TBLDEALS

WHERE CONVERT(VARCHAR(20),trade_date, 120) = '2009-03-06 00:00:00'

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

A Little Help Please SSCarpal Tunnel Points: 4764 More actions · Answer 7

This did not work

collation is: SQL_Latin)General_CP1_CI_AS

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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 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?

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'

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 10

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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.

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 12

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Okay, this topic has been double posted. I suggest any further discusion continue in this thread. To see what else has been suggested, look here.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 14

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 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?

Print the TOP 10 * results you're getting so folks can see what you see.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)