• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

How do is use 'like' on a timestamp column?

Sorry for asking a silly question: how do do this?
select * from table1 where start_time like '5/11%';
the value in start_time : '5/11/2011 10:47:51.776000 AM'
0
Rao_S
Asked:
Rao_S
  • 12
  • 10
  • 4
  • +1
1 Solution
 
slightwv (䄆 Netminder) Commented:
You 'can' convert the value to a string using to_char and do it that way.

The preferred method would be convert your string to a timestamp/date and return the rows that way:

select * from table1 where start_time >= to_date('05/11','MM/DD');
0
 
OP_ZaharinCommented:
- yours should work:

SELECT * FROM table1 WHERE start_time LIKE '5/11%';
0
 
Rao_SAuthor Commented:
Thank you!
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
sdstuberCommented:
best is to NOT use like

if you have a timestamp and you want to check a range,  then check against a timestamp range, not a string


select * from table1 where start_time >= to_timestamp('5/11','mm/dd')
and start_time < to_timestamp('5/11','mm/dd') + numtodsinterval(1, 'DAY')
0
 
sdstuberCommented:
note,  the accepted answer will also return 5/12/2011 and all other future days

which is not the same thing as what you asked for

also note,  the query I posted will only return data for 5/11 of the current year
which also is not the same thing you asked for.

if you want data for May 11 of all years, which is the only thing I can think your "like" criteria would specify then you will need to do a string conversion or multiple extraction of day and month
0
 
sdstuberCommented:
to do what you actually asked for,  may 11 for all years

then...


select * from table1 where to_char(start_time,'MM/DD')  = '05/11'

or...

select * from table1 where extract(day from start_time) = 11
and extract(monthfrom start_time) = 5
0
 
OP_ZaharinCommented:
- if you want to use the LIKE criteria, it would be:

 SELECT * from table1 WHERE to_char(start_time,'MM/DD')  LIKE '05/11%'
0
 
Rao_SAuthor Commented:
hi op_zaharin, sdstuber,
i tested both your comments, they test fine, thank you, these options are great!
0
 
sdstuberCommented:
but why use LIKE since it will be either exactly equal or not?

there is not wildcarding for a mm/dd formatted string when compared to 05/11

what would the '%'  apply to?
0
 
sdstuberCommented:
>>> they test fine,

this doesn't make sense,  they can't "all" be fine because they all do something different

the first post finds all timestamps from 5/11 of current year and greater
the second post requires an implicit conversion, meaning it wont work in all cases
the third post only returns data for 5/11 of the current year
the fifth post returns 5/11 for all years (this is what you asked for)
the sixth post also returns 5/11 for all years but uses LIKE which doesn't apply
0
 
Rao_SAuthor Commented:
not sure how the '%'  worked... but in both these cases, i got all the rows that have a start date of 5/11:
select * from log where to_char(start_time,'MM/DD')  LIKE '05/11%' ;
select * from log where to_char(start_time,'MM/DD')  = '05/11'
0
 
Rao_SAuthor Commented:
hold on let me try the other posts..
0
 
sdstuberCommented:
the LIKE works because the % doesn't do anything

it's not wrong,  it's just irrelevant

'05/11'  is like  '05/11%'  because it matches the first 5 characters and the % is nothing.

all timestamps you convert will either have 05/11 as the first 5 characters or they won't so the wildcard at the end is meaningless and a bit confusing
0
 
slightwv (䄆 Netminder) Commented:
If you want the specific day just add the year to the to_date and use '='.
0
 
OP_ZaharinCommented:
- i've been using LIKE on dates for many years before, BUT this depending for what purpose you use it for. of course usually we would use BETWEEN or <>= on date column. since we can't use LIKE on date, we convert it to string (to_char()) first.

- my date format is DD-MON-YYYY which then i convert the to_char() like this:
WHERE to_char(hiredate,'DD-MON') LIKE '08-SEP%'

- and i can also do this which also return the same result:
WHERE to_char(hiredate,'MON-DD') LIKE 'SEP-08%'
0
 
OP_ZaharinCommented:
- when using LIKE as below, it will return everything like '05' for the day of the date regardless of the month. and i agree, this is not the elegant way to use for handling dates, but in certain operation you need to use the LIKE, it is still possible.

WHERE to_char(start_time,'DD/MM')  LIKE '05%' ;
0
 
sdstuberCommented:
sorry, I guess I didn't explain my point well enough.

the LIKE in these examples is sort of pointless

    to_char(hiredate,'DD-MON') LIKE '08-SEP%'
    to_char(hiredate,'MON-DD') LIKE 'SEP-08%'

the to_char ensures the resulting string output will be exactly 6 characters long
regardless of what the date is.

so,  that string will NEVER match anything beyond the 6th character
in other words you could have written those conditions like this...

    to_char(hiredate,'DD-MON') LIKE '08-SEP'
    to_char(hiredate,'MON-DD') LIKE 'SEP-08'

but... if you don't have a wildcard, then the LIKE is just a more complicated equality check  so they should have been written like this...

    to_char(hiredate,'DD-MON') = '08-SEP'
    to_char(hiredate,'MON-DD') = 'SEP-08'

my point is NOT that the like can't work,  my point is that it's unnecessary and misleading in caess like this because the wildcarding implies greater variable functionality than really exists.



0
 
Rao_SAuthor Commented:
comming back to the same question...
when i run this:
select count(*) from errors where error_tmstp >= to_timestamp ('01-Oct-10 00:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF');  --count = 1626417,
when i run this:
select count(*) from aeher01.teh_er_errors where error_tmstp < to_timestamp ('01-Oct-10 00:00:00.000000', 'DD-Mon-RR HH24:MI:SS.FF');  --count = 1080186.
the total count on the table is: 2706718, i am missing 115 rows, why is that?
I have to delete all rows prior to Oct 10th.
0
 
Rao_SAuthor Commented:
the error_tmstp has the following format:
ERROR_TMSTP    TIMESTAMP(6) WITH TIME ZONE, --> 10/1/2010 12:07:11.000000 AM -04:00
0
 
sdstuberCommented:
that looks like a new question  what does that have to do with any of the "LIKE" or discussions above?

but, to give you something to look at... your queries are against 2 different tables
0
 
Rao_SAuthor Commented:
sorry, its the same table, in the first query, i just removed the real name of the table.
0
 
Rao_SAuthor Commented:
will open a new q for this question, but will keep this one open for now because, i still have to try out all the options..
0
 
sdstuberCommented:
please open a new question to pursue this,  do you have anything more about the "LIKE"  itself?  Does this question need to be reopened or is it done?
0
 
sdstuberCommented:
sorry, posting at same time.

but, does this question need to be reopened?
0
 
Rao_SAuthor Commented:
this one is done...
0
 
sdstuberCommented:
ok, I was just checking because of this phrase

 >>> will keep this one open for now

note, if you end up using something other than what you accepted, or in addition to that post,  please reopen and accept whichever posts that helped

0
 
Rao_SAuthor Commented:
yesterday, i used the first one which solved my problem, the other posts were all informational..and wanted to try them all, that is all..
0
 
sdstuberCommented:
ok,  I must have misunderstood the question then.

glad you got your answer
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 12
  • 10
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now