split string in sql query

Question

I have a value in field called "postingdate" as string in 2009-11-25, 12:42AM IST format, in a table named "Post".

I need the query to fetch the details based on date range. I tried the following query, but it throws an error. Please guide me to fix this issue. Thanks in advance.

select postingdate 
from post 
where TO_DATE(postingDate,'YYYY-MM-DD')>61689 
  and TO_DATE(postingDate,'YYYY-MM-DD')<61691

Answer 1

In SQL Server you can say

  Select postingdate from post 
  where postingdate between '6/16/1969' and '6/16/1991'

Answer 2

If it's really a string, you're lucky that it's in YYYY-MM-DD format. You can sort and compare that format as a string, because the most significant numbers are on the left side. For example:

select *
from Posts
where StringDateCol between '2010-01-01' and '2010-01-02'

There's no need to convert the string to a date, comparing in this way is not affected by the , 12:42AM IST appendage. Unless, of course, your table contains dates from a different time zone :)

Answer 3

You will need to convert your string into a date before you run date range queries on it. You may get away with just using the string if your not interested in the time portion.

The actual functions will depend on your RDBMS

for strings only
select * from posts
where LEFT(postingDate,10) > '2010-01-21'

or
for datetime ( Sybase example)
select * from posts
where convert(DateTime,postingDate) between '2010-01-21' and '2010-01-31'

Answer 4

As you've now seen, trying to perform any sort of query against a string column which represents a date is a problem. You've got a few options:

1. Convert the postingdate column to some sort of DATE or TIMESTAMP datatype. I think this is your best choice as it will make querying the table using this field faster, more flexible, and less error prone.

2. Leave postingdate as a string and use functions to convert it back to a date when doing comparisons. This will be a performance problem as most queries will turn into full table scans unless your database supports function-based indexes.

3. Leave postingdate as a string and compare it against other strings. Not a good choice as it's tough to come up with a way to do ranged queries this way, as I think you've found.

If it was me I'd convert the data. Good luck.