Question:
The table I am using stores date values as strings due to conversion data. The format is 2000-07-01 for July 1, 2000. I need to be able to age amounts for an accounting report at any time, such as 0-30, 30-60, etc. Normally, I would just use a statement such as DATE(current date) – DATE(table_date) between 30 and 60, but it won’t work because the “table-date” is not a DATE field. I’ve been hard-coding the dates and using the between statement, but that’s not good for other users.
Can I change the string ‘2000-07-01’ to a DATE field?
Answer:
Well, your column data type is varchar so you’ll have to do the calculations in your SELECT statement. You can use the CAST function to convert the string to a date. Let’s assume you have a table called TestTable with a datestring column and these values:
2000-06-012000-06-022000-06-15
You can use this query to find the aged records by comparing the datestring column to today’s date:
SELECT datestringFROM TestTableWHERE GETDATE() - CAST(datestring AS datetime)BETWEEN 30 AND 60
