Solved

ASP - SQL Server (Compare 2 dates)

Posted on 2009-06-28
35
581 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:Wayne Barron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 12
  • 4
  • +2
35 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24733267
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24733273
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
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24733281
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24733338
>>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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733347
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24733358
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 total points
ID: 24733363
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
 
LVL 15

Accepted Solution

by:
rob_farley earned 300 total points
ID: 24733374
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733464
@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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733465
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733500
asp classic VBA
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733521
ASP Classic, VB? VBA is within Office. You mean VB I think.

Rob

0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 50 total points
ID: 24733527
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733551
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733567
getting an error on     cmn
cmd.Parameters.Append cmn.CreateParameter("start",adDateTime)


Object required: 'cmn'
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733570
@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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733711
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733839
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733859
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733861
Figured that one out.
Forgot to add it to the SELECT statement.
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733866
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733873
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733895
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733921
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733927
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24733959
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24733966
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
 
LVL 31

Author Closing Comment

by:Wayne Barron
ID: 31597772
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24734027
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
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24734065
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24734126
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24734132
And... if you ask a separate question, please send me the link.

Rob
0
 
LVL 31

Author Comment

by:Wayne Barron
ID: 24734222
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24735844
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24740876
Ah... no adDateTime. It's clearly been too long since I've done it.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question