How to get a total days count

Question

Using MySQL & SQL Server

ID sDate

001 03/06/2010
002 07/08/2010
....
....

sDate Datatype is varchar Format mm/dd/yyyy

I want to take the date count means How many days is still there, It should compare the system date...

How to convert my varchar to datetime datatype, then how to compare the mysdate to system for getting the totalday counts

sDate - SystemDate = Totalday 

ExpectedOutput

ID sDate Totaldays

001 03/07/2010 3 days
002 07/07/2010 7 days
.....

How to make a query for this condition. Need query help.

Answer 1

Your question states MySQL & SQL Server so here is both:

SQL Server datediff function:

SELECT ID, DATEDIFF(DAY, GETDATE(), CONVERT(DATETIME, sDate)) FROM TABLE

MySQL datediff function:

SELECT ID, DATEDIFF(CURDATE(), STR_TO_DATE(sDate, '%c/%d/%Y')) FROM TABLE

This uses the STR_TO_DATE function to convert the varchar to a date and assumes that the format of your date strings is in the format month/day/year.

Answer 2

Gopal, in response to your "How to convert varchar to datetime in mysql...", it's easy:

ALTER TABLE sometable CHANGE sDate sDate datetime;

and MySQL will happily attempt to convert the values for you. However, if it can't properly parse the original date string, that record's sDate will get set to NULL or 0000-00-00. You'll have to massage the sDate field first to convert it to a more normal MySQL format for date strings, which is YYYY-MM-DD. A bruteforce hack would be:

UPDATE sometable SET sDate=CONCAT(
   SUBSTR(sDate, 6, 4),
   '/',
   SUBSTR(sDate, 3, 2),
   '/',
   SUBSTR(sDate, 0, 2)
);

Of course, this is assuming that your dates are in DD/MM/YYYY format. If they're MM/DD/YYYY, then just swap the middle and last SUBSTR calls. Once this update's completed, then you can use the ALTER TABLE to change field types.

And of course, for anything that affects the entire table like this, make sure you have a backup of it first.