CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
user_vs
user_vs

Reputation: 1033

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

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.

Upvotes: 4

Views: 13745

Answers (4)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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.

Upvotes: 2

Horaciux
Horaciux

Reputation: 6477

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.

Upvotes: 5

ZuoLi
ZuoLi

Reputation: 368

First, you can use convert function:

SELECT CONVERT(Datetime, '2014-10-30 18:00:00', 120)

Second, if you can't change the existing columns and their type, it does not mean that you can't add additional column with correct date type that duplicates the meaning of "wrong" column. This would both save your legacy code and help you in new development, as all the operations with convertation in queries are very expensive in terms of performance.

Upvotes: 0

juergen d
juergen d

Reputation: 204854

Use STR_TO_DATE()

select * from your_table
where str_to_date(fromdate, '%Y-%m-%d %H:%i:%s') >= '2014-09-10 10:10:10'
and str_to_date(todate, '%Y-%m-%d %H:%i:%s') <= '2014-10-10 10:10:10'

Upvotes: 1

Related Questions