0

I have an existing table that have a column date but varchar datatype and also a date datetime datatype

example:

tbl_name: trx1

columns: trx_id, activity, name, date(datetime)

index: date_of_birth

tbl_name: trx2

columns: trx_id, activity, name, date(varchar)

index: date_of_birth

is it ok to index date as varchar on trx2 and get the same expectation as trx1? OR

The best thing to do is to change the datatype to datetime also

Any comment or suggestion would be a great help TIA!

Improve this question
4
  • 1
    1) Use not verbal structures description but complete CREATE TABLE scripts. 2) What's the goal of such indexing? What operation/query should be improved? PS. If you store the date value than you'd use DATE datatype, not VARCHAR.
    – Akina
    Jan 10 at 4:38
  • we're trying to improve multiple UNION ALL using date thanks
    – Nullified
    Jan 10 at 4:41
  • 1
    If you use UNION ALL over DATETIME and VARCHAR datatypes combined in one output column then all DATETIME values are converted to VARCHAR which needs in additional resources. So altering the datatype is reasonable.
    – Akina
    Jan 10 at 4:44
  • thank you so much !
    – Nullified
    Jan 10 at 4:45

1 Answer 1

Reset to default
1

The best thing to do is to change the datatype to datetime also

Short Answer - Yes!
Always store data values in fields of the correct Data Type.
It simply avoids problems just like this and makes the full range of Data Type-specific functions available to you. For example, you can't do Date "arithmetic" on varchar fields (not without the inefficient and slow implicit type conversions and, probably, the Table Scanning that goes with it).

Also, why do you have two (or more) tables with the same structure?
The "Table-per-Thing" Model almost always breaks down. A single table, with an extra column to distinguish between populations and proper indexing can serve you just as well, if not better.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.