How to compare dates which is stored as String(varchar) in database?

Question

I have a database(table), in which 2 fields are:

fromdate varchar(20)
todate  varchar(20)

Dates are stored in this fashion:

YYYY-MM-DD HH:mm:ss

For ex: '2014-10-30 10:10:10' in database.

Now I want to compare two dates and fetch records from database by using query, 2014-09-10 10:10:10(fromdate) to 2014-10-10 10:10:10(todate)

How to fetch all accurate records.. Is there any kind of solution..

Thanks.

Answer 1

Just compare the string without extra overhead.

This format "YYYY-MM-DD HH:mm:ss" shares chronological and literal alphabetical order

SELECT * FROM someTable
WHERE fromdate >= '2014-09-10 10:10:10' AND todate <= '2014-10-10 10:10:10'

Also, I would create an index on those columns.

Answer 2

i have a database(table), in which 2 fields are: fromdate varchar(20) todate varchar(20)

It is a design flaw. Date should always be a DATE data type and never be string.

dates are stored in this fashion YYYY-MM-DD HH:mm:ss

DATE is never stored in any format. it is for us, human beings to understand.

Oracle stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:

Byte    Description
----    -------------------------------------------------
1       Century value but before storing it add 100 to it
2       Year and 100 is added to it before storing
3       Month
4       Day of the month
5       Hours but add 1 before storing it
6       Minutes but add 1 before storing it
7       Seconds but add 1 before storing it

for eg :"2014-10-30 10:10:10" in database.

Now i want to compare two dates and fetch records from database by using query, 2014-09-10 10:10:10(fromdate) to 2014-10-10 10:10:10(todate)

Just use to_date('2014-10-30 10:10:10', 'YYYY-MM-DD HH24:MI:SS')

NOTE This is for Oracle database. I see you have tagged SQL Server too. I don't understand why did you do that.