Solved

ASP - SQL Server (Compare 2 dates)

Posted on 2009-06-28
35
574 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
  • 15
  • 12
  • 4
  • +2
35 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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 30

Author Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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
Comment Utility
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 14

Accepted Solution

by:
rob_farley earned 300 total points
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
@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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
asp classic VBA
0
 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

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


Object required: 'cmn'
0
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
@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 30

Author Comment

by:Wayne Barron
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
Figured that one out.
Forgot to add it to the SELECT statement.
0
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
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 30

Author Closing Comment

by:Wayne Barron
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 30

Author Comment

by:Wayne Barron
Comment Utility
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 14

Expert Comment

by:rob_farley
Comment Utility
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 14

Expert Comment

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

Rob
0
 
LVL 30

Author Comment

by:Wayne Barron
Comment Utility
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
Comment Utility
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 14

Expert Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 69
how to fix this error 14 45
What's the difference between these two "qualifiers?" 3 29
Complex SQL 10 32
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now