Vfrdtyky

I have a table with a column that was previously converted using

(CONVERT(VARCHAR(19), GETDATE(), 112)

With the getdate being the date when the field was inserted. Now I have to compare the current date against the date the field was inserted.
The issue I'm facing is that when the date field is from last month, say 20131004, I calculate date difference by (CONVERT(VARCHAR(19), GETDATE(), 112) - 20131004, the result is 200. Obviously this is wrong...
Could you please suggest me how I could calculate the true date difference?

I agree with @Aaron that you should store a date field in a date field, not a text field.

If for some reason you do want to store it in a text field then the easiest way to calculate the number of days is to convert it back to a date field and then compare it:

SELECT DATEDIFF(DAY, CAST(column_name as DATETIME), GETDATE()) FROM dbo.table

This will throw an error if the value in the column cannot be converted to a date. You'll also need to make sure that the date is formatted correctly for your database. Assuming you use the format 112 you should be ok, but if you have the value 04/12/2013 in the column is that the 4th December 2013 or the 12th April 2013? It depends on how your database is configured.

But anyway, if you always insert dates in that field then you're nuts not making it a date field.

If you need to display the date somewhere then convert it on the way out.

1. You should be using the DATE or DATETIME data type for that column. Why on earth would you ever store a date as a string? Do you know how much you lose by doing so? Validation, for one - a VARCHAR(19) column will accept 20131004 12:34 PM but will also accept nonsense values like I am not a date!.



2. 
   

   If the data is actually good, you can simply do this instead of lazy shorthand and without any explicit conversions:

   
   
   

   SELECT DATEDIFF(DAY, column_name, GETDATE()) FROM dbo.table;
   
   

   
   
   

   If you get an error message with this, then you have bad data. You can identify it like this:

   
   
   

   SELECT column_name FROM dbo.table WHERE ISDATE(column_name) = 0;
   
   

   
   

Please read:

• Bad habits to kick : choosing the wrong data type




• Bad habits to kick : mis-handling date / range queries




• Bad Habits to Kick : Using shorthand with date/time operations