CODEWITHSUNDEEP
mysqlsql-likevarchar
Any
Any

Reputation: 75

mysql query with 'like' doesn't work with varchar and space

I have a Db with one table with 3 fields like the following:

user_id    TimeStamp    Azioni

where the 'timestamp' field is a varchar(25) like this: 2012/09/19 16:34:01.95 It is a varchar and not a timestamp value because i need it to be in the shown format. And i cannot change its type even if i wanted to.

Now, I'm trying to get all db entries with the same date. For example, when Timestamp contains 2012/09/19

I tied several queries:

Query 0:

SELECT  Azioni.Action
FROM Azioni
WHERE TimeStamp LIKE '2012/09/19%'

Query 1:

SELECT `Azioni`.*
FROM Azioni
Where `TimeStamp` LIKE  '{2012/09/19}%'

Query 2:

SELECT  `Azioni` . * 
FROM Azioni
WHERE LOCATE(  '2008/09/19',  `TimeStamp` ) >0

Query 3:

SELECT  `Azioni` . * 
FROM Azioni
WHERE INSTR(  `TimeStamp` ,  '2012/09/19' ) >0

Query 4:

SELECT * FROM `Azioni` 
WHERE `TimeStamp` like '2012|/09|/19%' escape '|'

and I always get: MySQL returned an empty result set (i.e. zero rows).

But I am sure there are rows containing the said timestamp. What am i doing wrong? Does the 'space' between date and time create a problem? If so how can i solve it? Do you have any suggestion?

EDIT: Aa suggested, from 

SELECT TIMESTAMP, HEX( TIMESTAMP ) 
FROM Azioni

i get the following

2009-06-06 09:28:00.0000    323030392D30362D30362030393A32383A30302E30303030
2009-06-06 09:29:00.0000    323030392D30362D30362030393A32393A30302E30303030
2009-06-06 09:30:51.0000    323030392D30362D30362030393A33303A35312E30303030
2009-06-06 14:25:00.0000    323030392D30362D30362031343A32353A30302E30303030
2009-06-06 14:26:00.0000    323030392D30362D30362031343A32363A30302E30303030

EDIT 2:

ehm yeah, i was typing the date wrong in the query. Sigh, i'm stupid. Sorry for wasting your time guys.

Upvotes: 2

Views: 1934

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

How about this:

where timestamp like '2012/09/19%'

And, if you are going to call the field timestamp you should store it as a date/datetime/timestamp. Call it something else if it is going to be stored as a string. Timestamp is actually the name of a type in MySQL, so having that in a column name with a different type is quite misleading.

EDIT:

Have you tried:

where left(timestamp, 10) = '2012/09/19'

It sounds like there are string characters in the field, which are preventing reasonable code from working.

Upvotes: 2

1GDST
1GDST

Reputation: 821

SELECT * FROM Azioni WHERE `TimeStamp' LIKE '2012/09/19%'

Upvotes: 0

Related Questions