SQL SELECT statement error with datetime

Question

Something is wrong with statements with datetime field:

MyDB:

id  name   datetime
1   test1  2011-04-28 19:37:44
2   test2  2011-04-28 21:27:04

When I run the following statement

SELECT * 
FROM myTable 
WHERE ('datetime' > '2011-04-28 21:00:00')

I get all - test1 and test2

When I run this statement

SELECT * 
FROM myTable 
WHERE ('datetime' = '2011-04-28 21:27:04')

I get nothing

When I run the following statement

SELECT * 
FROM myTable 
WHERE ('datetime' > '2011-04-28 21:00:00')
    AND ('datetime' < '2011-04-29 21:00:00')

I get nothing

Why?

are you sure your datetime field is a real datetime field, and not varchar? What db are you using? — M.R.
– M.R., Commented Apr 28, 2011 at 20:14

David Fells · Accepted Answer · 2011-04-28 20:13:20Z

1

You are putting the field name "datetime" into quotes and MySQL treats it as a string, not a column.

SELECT * FROM myTable WHERE datetime > '2011-04-28 21:00:00';

answered Apr 28, 2011 at 20:13

David Fells

6,8081 gold badge24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

+1 Actually every DBMS treats 'datetime' as a string literal and datetime as a column name.

tobias86 · Accepted Answer · 2011-04-28 20:13:46Z

1

You are doing string comparisons, i.e. comparing the string "datetime" to a certain date.

Change your query to

...WHERE ([datetime] = '2011-04-28 21:27:04')

answered Apr 28, 2011 at 20:13

tobias86

5,0291 gold badge23 silver badges31 bronze badges

Comments

Jaymz · Accepted Answer · 2011-04-28 20:14:02Z

0

Remove the quotes around the column name:

SELECT * FROM myTable WHERE (datetime > '2011-04-28 21:00:00')

In your query, you are comparing the string "datetime" against another string, consisting of numbers.

Alphanumerically, numbers come before letters, so the "datetime" string is greater than the numeric date string you are comparing against.

answered Apr 28, 2011 at 20:14

Jaymz

6,3982 gold badges29 silver badges30 bronze badges

Comments

FrustratedWithFormsDesigner · Accepted Answer · 2011-04-28 21:06:40Z

0

Are you actually putting the datetime column name in single quotes? If you are, it means you are comparing the string 'datetime' to the string '2011-04-28 21:00:00' which doesn't make much sense. In SQL, if something is surrounded in single quotes it is usually a string literal.

Have you tried:

SELECT * FROM myTable WHERE datetime > '2011-04-28 21:00:00'

Or more likely:

SELECT *
FROM myTable
WHERE datetime > to_date('2011-04-28 21:00:00','YYYY-MM-DD HH24:MI:SS');

Note: That to_date function and the format string are specific to Oracle, you did not specify which database you are using and these functions tend to be db-specific.

edited Apr 28, 2011 at 21:06

answered Apr 28, 2011 at 20:13

FrustratedWithFormsDesigner

27.7k31 gold badges151 silver badges211 bronze badges

Collectives™ on Stack Overflow

SQL SELECT statement error with datetime

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related