Selecting by Date only in Date-Timestamp problem 2

[Wyldcard9]

I am trying to get only a certain date from a field with a datetime stamp. When I look at the data in SQL Server I see:

8/9/2004 3:26:29 PM

In the help files it says the format is:
yyyy-mm-dd hh:mm:ss[.fff]

I have tried ever format I can think of.

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 10) = '2004/08/09'

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 10) = '2004-08-09'

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 8) = '2004/8/9'

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 10) = '20040809'

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 6) = '200489'

And also in the other format MMDDYYYY. Every run returns nothing. What am I missing?

 

[NoCoolHandle]

THe date include milliseconds

you ususlly need to do a between date1 and date2 to get records for date1

 

[Wyldcard9]

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 8) > '8/9/2004'
and LEFT(ID_502, 9) < '8/10/2004'

is also returning nothing

 

[NoCoolHandle]

Sorry I needed to read your question more carefully and hit Submit..

My question is what do you get when you run the following query?

select top 1 LEFT(ID_502, 10) from .[EP3_14]

 

[Wyldcard9]

NoCoolHandle,
I get one row in return
Oct 13 200

 

[Wyldcard9]

NoCoolHandle,

Thanks.

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 11) = 'Aug 9 2004'

worked perfectly. I did not know the correct format of the field.

Another question for you. What is the opposite key word to 'TOP'?
I could not find 'BOTTOM' as a key word. Is there one?

 

[cLFlaVA]

The opposite of TOP is TOP with the ORDER BY clause changed.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[Wyldcard9]

cLFlaVA,
Thanks

 

[john76]

Try:

Select  *
  From  [elvis].[EP3_14]
 Where  Convert(varchar, ID_502, 101) = '08/09/2004'

HTH,
John

 

[NoCoolHandle]

Just because I want to throw a spanner in the works...

This column isnt datetime is it?

And by doing a left(column,10) you are forcing SQL SErver to do a table scan, no index will work if you need to run a calculation on each row before you can test it against the criteria.

Performance should really suck

Was the col data datetime or is it date with other values added?

Rob

 

[Wyldcard9]

SELECT *
FROM .[EP3_14]
WHERE LEFT(ID_502, 11) = 'Aug 9 2004'

worked exactly the way I was expecting it to, and ran pretty quick off of 20K records, returning the 182 I wanted. That was what I was looking for. When you had me do the select with the top, it pointed out that I did not have the right format of the date.

The field is datetime. I really do not care about the time.

 

[ESquared]

If the field is datetime then in my mind the proper way to do it is,

WHERE ID_502 >= '2004-08-09' AND ID_502 < '2004-08-10'

Converting the datetime field (which is *numeric*, not character) to a character field is expensive. It is good practice to use this other form as indexes can be used, and no functions or conversions have to be done for every row in the entire table.

Notice the >= and <, and also note that the reason this works is because if ID_502 is really a datetime column then SQL server will implicitly convert the string to datetimes. If you had to do it explicitly, this would be the way:

WHERE ID_502 >= Convert(datetime,'2004-08-09') AND ID_502 < Convert(datetime,'2004-08-10')

-------------------------------------
A sacrifice is harder when no one knows you've made it.