ASP - SQL Server (Compare 2 dates)

Not quite clear on your question. Is "mydates" field a datetime or a varchar?

If it is SQL Server datetime field it is simple as:

select * from d
  where mydates between '2009-06-28' and '2009-06-29'

select * from d
  where mydates between '06/28/2009' and '07/29/2009'

In SQL Server, the query would look like

SELECT MyDates
FROM urtable
WHERE MyDates Between convert(datetime,'mm/dd/yyyy', 101)
And convert(datetime,'mm/dd/yyyy', 101)

Assuming MyDates column is of Datatype Datetime, replace first value with the lower range and the second one with the higher range.

Hope this helps

Or assuming myDates is a varchar()

select * from d where cast(mydates as datetime) between '06/28/2009' and '06/29/2009'

Though this is not good for performance since an index will not be used so I recommend converting the column to a real datetime

>>This works great in Access.
But, how is this done in SQL Server.<<
Except for the use pf a a single quote instead of # no different:
sql = "SELECT MyDates WHERE MyDates Between '" & strFristDate &"' AND '" & strLastDate &"'"

Since you have to use a character value for a date time, you would be wise to use an unambiguous format, such as:
YYYYMMDD

or use the CONVERT function as in:
sql = "SELECT MyDates WHERE MyDates Between " & CONVERT(datetime, strFristDate, 101) &" AND " & CONVERT(datetime, strLastDate, 101)

sorry, It is a datetime field. Not a VarChar.
That was my mistake and I do appologise.

OK.
This code from: rrjegan17:
SELECT MyDates
FROM urtable
WHERE MyDates Between convert(datetime,'mm/dd/yyyy', 101)
And convert(datetime,'mm/dd/yyyy', 101)

When I run it like so:

SELECT MyDates
FROM urtable
WHERE MyDates Between convert(datetime,'strFirstDate', 101)
And convert(datetime,'strLastDate', 101)

I get the following error:

=-=-=-=-=-=-=-=-=-=-=-
Conversion failed when converting datetime from character string.
=-=-=-=-=-=-=-=-=-=-=-

Do I need to add in something around the    strFirstDate & strLastDate
Instead of  'strFirstDate' ?

And this:
sql = "SELECT MyDates WHERE MyDates Between " & CONVERT(datetime, strFristDate, 101) &" AND " & CONVERT(datetime, strLastDate, 101)

Should have read:
sql = "SELECT MyDates WHERE MyDates Between " & CONVERT(datetime, " & strFristDate & " , 101) &" AND " & CONVERT(datetime, " & strLastDate & ", 101)

@rob
>>For a start, you're missing a FROM clause in the original question.
It happens some times. It was written out for this post only, not actual code being used.

>>sql = "SELECT convert(varchar(10),MyDates, 101) AS MyDateString FROM DatesTable WHERE MyDates Between '20090628' AND '20090629' "

Tried it, did not work.

>sql = "SELECT MyDates FROM DatesTable WHERE MyDates >= @start AND MyDates < @end "
this is have used before, but not with SQL Server. (And not with the @)
And I get the following error when I use this one.
>>>>>>       Must declare the scalar variable "@strFristDate".

strFirstDate = Gets the date from a Dropmenu in the format of: 01/01/1971  mm/dd/yyyy
strLastDate = Gets the date from a Dropmenu in the format of: 01/01/1995  mm/dd/yyyy

So that I can get the results of the records of anything between these 2 dates.

I am brand new to SQL Server and it's commands.
And I just did some checking on this error, and am completely lost as to what is going on with it.

-----------
@AC
I tried yours as well, and it did not return no results either.

What environment are you using? VB.Net? VBA? C#?

Using variables like I did will mean you need to set up some sort of Parameters collection to pass in.

Rob

asp classic VBA

ASP Classic, VB? VBA is within Office. You mean VB I think.

Rob

You want something like this:

Set cmd= Server.CreateObject("ADODB.Command")
cmd.CommandText = "SELECT convert(varchar(10),MyDates, 101) AS MyDateString FROM DatesTable WHERE MyDates Between ? AND ? "

cmd.Parameters.Append cmn.CreateParameter("start",adDateTime)
cmd.Parameters("start").Value = startDate
cmd.Parameters.Append cmn.CreateParameter("end",adDateTime)
cmd.Parameters("end").Value = endDate

getting an error on     cmn
cmd.Parameters.Append cmn.CreateParameter("start",adDateTime)


Object required: 'cmn'

@rrjegan17:
Still does not work, as AC pointed out above.
Need
convert(datetime,"&strLastDate&", 101)
To make it compile, but it still gives no results.

OK Rob.
I changed your
cmn
to
cmd
And I get this error

Parameter object is improperly defined. Inconsistent or incomplete information was provided.

Any information on this issue?

Sorry - the cmn was a typo. I meant cmd.

It's been a long time since I used Classic ASP - but the approach of using parameters in this way is definitely right. I think I'm just missing something.

try:

cmd.Parameters.Append cmd.CreateParameter("start",adDateTime, adParamInput)
cmd.Parameters("start").Value = startDate
cmd.Parameters.Append cmd.CreateParameter("end",adDateTime, adParamInput)
cmd.Parameters("end").Value = endDate

instead... (with the adParamInput to indicate it's an input parameter).

Rob

Got another one for your
(Changed the " start " with  "MinAge" as it is the name of the Form Field. )

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

On this line
cmd.Parameters.Append cmd.CreateParameter("MinAge",adDateTime, adParamInput)

(This feels so strange to "not" be able to figure out my own errors.
SQL Server is going to Kick my butt big time)

Carrzkiss

Figured that one out.
Forgot to add it to the SELECT statement.

humm.
Did not matter, still get the same error
>>Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

on this line
cmd.Parameters.Append cmd.CreateParameter("MinAge",adDateTime, adParamInput)

I recommend you start by getting your query working with SSMS (Management Studio), using proper values instead of parameters.

Then, get it working with:

cmd.CommandText = "SELECT convert(varchar(10),MyDates, 101) AS MyDateString FROM DatesTable WHERE MyDates Between '20000101' AND '20010101' "

(without parameters).

Then see if you can add parameters to the mix, so that you don't need to construct your query every time (and potentially become the victim of SQL Injection).

Rob

I had already done that earlier, and it worked like a charm.
listed the records for the dates that I entered. as:
between '01/01/1971' and '01/01/1994'

Worked great.
Just cannot pass the parameters.

Trust me Rob.
I am not new at the Debugging part of it.
That was the first thing that I did prior to post here.
I covered my tracks.
Just cannot get the parameters to pass through.

The easy way of doing this is to create a stored procedure. Try this on the SQL box:

create proc dbo.ListDates(@mindate datetime, @maxdate datetime) as
SELECT convert(varchar(10),MyDates, 101) AS MyDateString FROM DatesTable WHERE MyDates Between @mindate and @maxdate;

Then try:
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter("@MinDate",adDateTime, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@MaxDate",adDateTime, adParamInput)
cmd.Parameters("@MinDate").Value = dMinDate
cmd.Parameters("@MaxDate").Value = dMaxDate

There are a ton of examples on the web about using parameters with stored procedures - one is bound to work for you... but certainly it's easier than using in-line parameters.

Rob

This produces the results that I am needing

SELECT     CONVERT(varchar(10),MyDates,101) AS MyDateTime
FROM         Members
WHERE     (MyDates >= '01/01/1969') AND (MyDates <= '01/01/1994')

Ok, brilliant. Now make a stored procedure on the SQL database like this:

create proc dbo.ListDates(@mindate datetime, @maxdate datetime) as
SELECT     CONVERT(varchar(10),MyDates,101) AS MyDateTime
FROM         Members
WHERE     (MyDates >= @mindate) AND (MyDates <= @maxdate)


And then see if you can use the adCmdStoredProc code to pass in the values you need.

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "dbo.ListDates"
cmd.Parameters.Append cmd.CreateParameter("@MinDate",adDateTime, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("@MaxDate",adDateTime, adParamInput)
cmd.Parameters("@MinDate").Value = dMinDate
cmd.Parameters("@MaxDate").Value = dMaxDate

This works.
I had the blasted things turned around.
I had it like this
 (MyDates >= '"&MinAge&"') AND (MyDates <= '"&MaxAge&"')

I then got to looking at my QueryString and notices that it was being passed differently.
So I changed them around, and wa-la.
It works now.

As this...........

SELECT     CONVERT(varchar(10),MyDates,101) AS MyDateTime
FROM         Members
WHERE     (MyDates >= '"&MaxAge&"') AND (MyDates <= '"&MinAge&"')


I want to think everyone that come in here tonight.
I think that I should have left this one alone until tomorrow, and I would have probably not
Made this simple annoying mistake.

I am going to split the points between everyone here, as everyone tried to help me figure out
Something that I already knew.

Have a good one
Carrzkiss

Thinks all for the great advice and some learned information.
It is going to come in very handy in the days to come.
As I learn this Beast we call: SQL Server 2005

Have a good one.
Carrzkiss

Cool... but please try to use parameters when you can. You're constructing a string there, which may end up leaving you vulnerable to SQL Injection attacks.

Rob

Yes thank you.
About the SQL Injection.
I know that it is different from Access Database,
And that doing it in SQL Server (Preventing it that is) Is way different.

I am going to put up a post about it.
So if you can assist in that, that would be great as well.

Thanks Rob.
Carrzkiss

It's simple - just use Parameters in your query. There's lots of information on the web about it.

For example, at:
http://www.owasp.org/index.php/Reviewing_Code_for_SQL_Injection

Hmm... maybe the problem was my use of Value. Try this:
cmd.Parameters.Append cmd.CreateParameter("@MinDate",adDateTime, adParamInput, , dMinDate)
cmd.Parameters.Append cmd.CreateParameter("@MaxDate",adDateTime, adParamInput, , dMaxDate)

(yes, that's two commas in a row, to indicate that the 4th value isn't required).

Rob

And... if you ask a separate question, please send me the link.

Rob

I am sorry that I did not get in here to post the link.
But I see that you have already posted to the other quest.

I will check out the information tomorrow, need some rest tonight
So I can better focus.

have a good evening Rob.
Carrzkiss ( USA EST 3:47AM )

rob_farley,

>>ASP Classic, VB? VBA is within Office. You mean VB I think.<<
It is not VB, it is VBScript.

>>maybe the problem was my use of Value. Try this:<<
Actually your use of the Value parameter was correct, although I prefer the syntax from your last comment.  The problem is that there is no ADO constant adDateTime.  For datetime data types the correct ADO constant is adDBTimeStamp (and in case you are wondering, what is the one for the TimeStamp data type that would be adBinary).  The complete list is here:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

It also should be pointed out that in ASP you need to include ADOVBS.inc or explicitly define your ADO constants.

Ah... no adDateTime. It's clearly been too long since I've done it.