help with asp/sql convert data to date for comparison

First off thanks in advance for any assistance.

This is what I'm trying to do. I have a database with 3 columns,
fMonth, fDay, and fYear. I'm trying to create an sql statement with
asp/vb to select these values and convert them to a date, and then
compare them using 'between' to two other values coming from a form,
date1 and date2.

something like this, although it's so wrong.

"select * from table where
convert(nvarchar,flightmonth+flightDay+flightYear,101) between '" &
myDate1 & "' and '" & myDate2 & "'"

please help, i am at a loss.


*** Sent via Developersdex http://www.developersdex.com ***

Re: help with asp/sql convert data to date for comparison

Kevin McGovern wrote:
> First off thanks in advance for any assistance.
>
> This is what I'm trying to do. I have a database with 3 columns,

What database? Type and version please.

> fMonth, fDay, and fYear. I'm trying to create an sql statement with
> asp/vb to select these values and convert them to a date, and then
> compare them using 'between' to two other values coming from a form,
> date1 and date2.
>
> something like this, although it's so wrong.
>
> "select * from table where

Don't use selstar: http://www.aspfaq.com/show.asp?id=2096

> convert

Ah! It must be MS SQL Server (I think - please don't make us guess)

> (nvarchar

Why "nvarchar"? Will you be handling international characters here? The
answer is no. Use the more efficient varchar datatype.

> ,flightmonth+flightDay+flightYear,101)

No errors? Are these columns character or numeric? I would have assumed they
were numeric, but ...
It always helps to tell us a little about your table structure, especially
the datatypes of the columns involved, and show us a few rows of sample data
(http://www.aspfaq.com/show.asp?id=5006)

If the columns are numeric, then you need to either convert them to
character before concatenating them, or do some arithmetic which will
usually be faster.

> between '" &

> myDate1 & "' and '" & myDate2 & "'"
>
> please help, i am at a loss.
>
>
You would be better off using a single indexed datetime column, preferably a
clustered index, but I assume you knew that (or is that a bad assumption?).
Any query you write that combines these columns for use in comparsons will
be extremely poorly performing. So, my first suggestion is to ditch these
columns and store proper datetime values in a single column. Hopefully you
can/will take this advice. If for some reason you can't, read on ...

Given that you are making the mistake of using dynamic sql (more on this
later), you are attacking this problem from the wrong starting point. You
should be using SQL Query Analyzer (QA) to construct a query that works, so
you will know what the query needs to look like when you build it in asp
using dynamic sql.

This will force you to learn how to use T-SQL to properly handle datetimes.
Read up on the datetime datatype in BOL, especially an article entitled
"Using Date and Time Data". Do some experimenting. Run this sql in QA to
see what you were actually creating:

SELECT convert(nvarchar,flightmonth+flightDay+flightYear,101)
FROM table

Adjust that until you are creating proper datetimes. Assuming these columns
are integer, and assuming flightyear contains proper 4-digit years - you've
got more problems if it doesn't) the first step is to convert the data to
character, at the same time formatting it into a recognizable format so that
sql can convert it to datetime. SQL will always recognize the ISO fomat:
yyyymmdd, so that is the format you whoould be striving for. With numeric
data, I suggest using arithmetic to get the data into the correct format.
Try this:

SELECT flightyear *10000 + flightmonth * 100 +
flightDay
FROM table

You should see data in the proper format. It's still integer data, but it's
in the proper format. You could stop here for this particular problem, but
just in case you ever need to use this data for any sort of datetime
arithmetic, I will go to the end. The next step is to convert the numbers to
characters (sql cannot directly convert numbers to datetime):

SELECT
CAST((flightyear *10000 + flightmonth * 100 +
flightDay) AS char(8))
FROM table

When you run this, you will now see properly formatted character data. Now,
it's childplay to convert it to datetime:

SELECT
CAST(
CAST((flightyear *10000 + flightmonth * 100 +
flightDay) AS char(8))
AS datetime)
FROM table

Now you see how to create proper datetimes from numeric data. However, in
this particular case, properly formatted integers will work as well as real
datetimes. We don't want to add extra performance hurdles by performing
unnecessary conversions (this query is already going to run slower than it
should), so I suggest using intgers for comparison:

SELECT
FROM table
WHERE
(flightyear *10000 + flightmonth * 100 + flightDay)
between 20050601 and 20050630


Now you have a query that works correctly (assuming all my assumptions were
correct), so now you know what the statement you plan to build in asp needs
to look like. Start by formatting the data in mydate1 and mydate2 into the
proper numeric format (again - yyyymmdd - see
http://www.aspfaq.com/show.asp?id=2313). Then, given that you will continue
to make the mistake of using dynamic sql, do this:

<%
'get the values for mydate1 and mydate2 and properly format them.
'then:
dim sSQL
sSQL = "SELECT " & _
"FROM table " & _
"WHERE (flightyear *10000 + flightmonth * 100 + flightDay) " & _
"between " & mydate1 & " and " & mydate2

'********the most important step*******************************
response.write sSQL
response.end
'*********************************************************** *******
%>

Run this page and verify that the statement written in the browser window
resembles the statement that worked correctly in QA. In fact, test the
statement you created by copying it from the browser window and pasting it
into the QA window. If you've done your concatenation correctly, it will run
without modification in QA.

Now you have a dynamic sql statement that can be executed. You can now
create and open your connection object execute the statement to retrieve a
recordset. This is the proper order of operation: Do not open your
connection until all the preliminaries are performed. The idea is to connect
to the database for as short a time as possible.

But you must be wondering why I keep saying it's a mistake to use dynamic
sql. While there are some performance issues with using dynamic sql
(increased network traffic, bloated procedure cache in SQL Server, etc.) my
main issue is security. See these links:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23


My preference is to use stored procedures. Aaron's written a pretty good
article to get you started with stored procedures here:
http://www.aspfaq.com/show.asp?id=2201

However, while the advice about creating stored procedures (and the reasons
why to use them) is impeccable, I disagree with some of the advice he gives.
Mainly, the advice to use dynamic sql to execute your stored procedures. I
cover this ground in this post:
http://tinyurl.com/jyy0


If for some reason you cannot use stored procedures with your application,
then try this alternative:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e


HTH,
Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: help with asp/sql convert data to date for comparison

Perhaps something like this could work:

strSQL = "SELECT * FROM MyTable WHERE
DateSerial(flightYear, flightMonth, flightDay) BETWEEN #" &
MyStartDateVBScriptVariable & "# AND #" & MyEndDateVBScriptVariable &
"#"

<<
First off thanks in advance for any assistance.

This is what I'm trying to do. I have a database with 3 columns, fMonth,
fDay, and fYear. I'm trying to create an sql statement with asp/vb to
select these values and convert them to a date, and thencompare them
sing 'between' to two other values coming from a form,
date1 and date2.

something like this, although it's so wrong.

"select * from table where
convert(nvarchar,flightmonth+flightDay+flightYear,101) between '" &
myDate1 & "' and '" & myDate2 & "'"

please help, i am at a loss.
>>

Best regards,
J. Paul Schmidt, Freelance ASP Web Designer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex http://www.developersdex.com ***

Re: help with asp/sql convert data to date for comparison

Bullschmidt wrote:
> Perhaps something like this could work:
>
> strSQL = "SELECT * FROM MyTable WHERE
> DateSerial(flightYear, flightMonth, flightDay) BETWEEN #" &
> MyStartDateVBScriptVariable & "# AND #" & MyEndDateVBScriptVariable &
> "#"

I think he's using sql server, not jet (see "nvarchar")

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"