Convert Character Type Date to use DateDiff function

[movieking81]

Hello,

I have gone through some of the other solutions to similar problems posted here, but none seemed to work for me. So, as a last ditch effort I posting this in hopes someone can help. This is what I have.

SQL table
column terminateddate - varchar type with date stored as mm/dd/yyyy

On an ASP page I'm trying to put together a SELECT statement that compares two dates, the one in the table and a requested variable. Here is what I have so far...

SQL = "SELECT * FROM table where datediff(d,Convert(varchar(20),Convert(datetime,te rminateddate), 101),""&requestedvariable&"") < 30 ORDER by lastname"

However, no matter how I change it all I get is the same error (below).

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Am I doing something wrong, any help would be great. I have several other projects to get down and this is holding me up.

Thanks

[ck9663]

Hello,

I have gone through some of the other solutions to similar problems posted here, but none seemed to work for me. So, as a last ditch effort I posting this in hopes someone can help. This is what I have.

SQL table
column terminateddate - varchar type with date stored as mm/dd/yyyy

On an ASP page I'm trying to put together a SELECT statement that compares two dates, the one in the table and a requested variable. Here is what I have so far...

SQL = "SELECT * FROM table where datediff(d,Convert(varchar(20),Convert(datetime,te rminateddate), 101),""&requestedvariable&"") < 30 ORDER by lastname"

However, no matter how I change it all I get is the same error (below).

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Am I doing something wrong, any help would be great. I have several other projects to get down and this is holding me up.

Thanks

either (or both) of your variable have an invalid date and take note of this from online help:

"DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years."

[fplesco]

Hello,

I have gone through some of the other solutions to similar problems posted here, but none seemed to work for me. So, as a last ditch effort I posting this in hopes someone can help. This is what I have.

SQL table
column terminateddate - varchar type with date stored as mm/dd/yyyy

On an ASP page I'm trying to put together a SELECT statement that compares two dates, the one in the table and a requested variable. Here is what I have so far...

SQL = "SELECT * FROM table where datediff(d,Convert(varchar(20),Convert(datetime,te rminateddate), 101),""&requestedvariable&"") < 30 ORDER by lastname"

However, no matter how I change it all I get is the same error (below).

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Am I doing something wrong, any help would be great. I have several other projects to get down and this is holding me up.

Thanks

Hey -

You may try this one

SELECT * FROM table 
where datediff(d, Convert(char(10),terminateddate, 121),CONVERT(char(10), " & requestedvariable &",121)) < 30 ORDER by lastname