ASP - SQL Server (Compare 2 dates)

Hello All;
I know how to do this in Access.

sql = "SELECT MyDates WHERE MyDates Between #"&strFristDate&"# AND #"&strLastDate&"#"
(In Access, the Field   MyDates = mm/dd/yyyy )

This works great in Access.
But, how is this done in SQL Server.
As I have just found out that in SQL Server there is not a way to set the
datetime = mm/dd/yyyy
As you can in Access, so you have to do a Convert in the code.

convert(varchar(10),'MyDates', 101)

How would this all work together to do the comparisions?

Thanks all.
Carrzkiss
LVL 31
Wayne BarronAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
rob_farleyConnect With a Mentor Commented:
I think there's some confusion here.

For a start, you're missing a FROM clause in the original question.

It sounds to me like you're just trying to format the output. In which case, you're right. You want your final query to be something like:

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

But it's nicer to use parameters, and it's generally better to have the formatting done by the application that's calling it. Also, BETWEEN isn't recommended with dates (better to use >= and <).

So I'd rather you did:

sql = "SELECT MyDates FROM DatesTable WHERE MyDates >= @start AND MyDates < @end "

...and then pass in a Parameters object (what are you calling this from?) to provide @start and @end, where @end is actually the start of the next period (and not included in the results). Eg, @start could be 20090601, and @end could be 20090701 (not 20090630).

Hope this helps,

Rob
0
 
mrjoltcolaCommented:
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'


0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mrjoltcolaCommented:
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
0
 
Anthony PerkinsCommented:
>>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)
0
 
Wayne BarronAuthor Commented:
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' ?
0
 
Anthony PerkinsCommented:
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)
0
 
Anthony PerkinsConnect With a Mentor Commented:
Let's try that again (not sure what I was thinking):
sql = "SELECT MyDates WHERE MyDates Between CONVERT(datetime, " & strFristDate & " , 101)  AND  CONVERT(datetime, " & strLastDate & ", 101)"
0
 
Wayne BarronAuthor Commented:
@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.
0
 
rob_farleyCommented:
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
0
 
Wayne BarronAuthor Commented:
asp classic VBA
0
 
rob_farleyCommented:
ASP Classic, VB? VBA is within Office. You mean VB I think.

Rob

0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
SELECT MyDates
FROM urtable
WHERE MyDates Between convert(datetime,'strFirstDate', 101)
And convert(datetime,'strLastDate', 101)

Hope your strFirstDate and strLastDate are string variables and holds date values in the format mm/dd/yyyy and make sure that it holds valid date values within this format.

If the date supplied for this variables in this format is not valid, then you will get those conversion errors.
0
 
rob_farleyCommented:
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

0
 
Wayne BarronAuthor Commented:
getting an error on     cmn
cmd.Parameters.Append cmn.CreateParameter("start",adDateTime)


Object required: 'cmn'
0
 
Wayne BarronAuthor Commented:
@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.
0
 
Wayne BarronAuthor Commented:
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?
0
 
rob_farleyCommented:
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
0
 
Wayne BarronAuthor Commented:
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
0
 
Wayne BarronAuthor Commented:
Figured that one out.
Forgot to add it to the SELECT statement.
0
 
Wayne BarronAuthor Commented:
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)
0
 
rob_farleyCommented:
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
0
 
Wayne BarronAuthor Commented:
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.
0
 
rob_farleyCommented:
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
0
 
Wayne BarronAuthor Commented:
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')
0
 
rob_farleyCommented:
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

0
 
Wayne BarronAuthor Commented:
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
0
 
Wayne BarronAuthor Commented:
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
0
 
rob_farleyCommented:
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
0
 
Wayne BarronAuthor Commented:
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
0
 
rob_farleyCommented:
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

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

Rob
0
 
Wayne BarronAuthor Commented:
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 )
0
 
Anthony PerkinsCommented:
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.
0
 
rob_farleyCommented:
Ah... no adDateTime. It's clearly been too long since I've done it.
0
All Courses

From novice to tech pro — start learning today.