1

We are refactoring a weird system where within its backend, values that should be stored as number and date columns, with propperly designed constraints, were stored all as varchar (using only UI validation). The thing is, while we do this the new system our ones must start to run so:

Is it possible to manipulate this varchar columns as integer for say lower or greater comparisons and, given that the dates are stored with the same format dd/MM/yyyy is it possible to treat that also varchar column as a date and use functions as: IN BETWEEN?

Erre Efe
  • 15,387
  • 10
  • 45
  • 77

2 Answers2

2

You could add a computed column (or a view with a column) like:

CONVERT(DATE, column_name, 103)

For performance concerns you could persist and/or index the computed column.

However if you are going to change the structure may as well just fix the data type in the first place.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1

Would something like this work:

SELECT Column1, Column2
FROM Table
WHERE CONVERT(datetime, DateColumn, 3) BETWEEN @StartDate AND @EndDate
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • One question: Based on your experience, has this a big performance penalty or isn't such a concern. Thanks. – Erre Efe Jun 04 '12 at 18:48
  • Performance for this should not be a big concern, unless you have a whole bunch of data and are already waiting a long time for results. – Gordon Linoff Jun 04 '12 at 19:26
  • This is not sargable if that is what you are asking. If you are dealing with a large number of records and you will be executing this query a lot, I would recommend creating an actual date time column so you don't need to do the CONVERT. – Abe Miessler Jun 04 '12 at 20:16
  • @AbeMiessler Sure, that's what we did but for the our software version and while we finish deployment we need to fix legacy using this. THanks. – Erre Efe Jun 05 '12 at 01:36