More Date Confusion

[Dooza]

I have been trying to follow the recent posts about the date format. I
am building a single page report, which displays 4 columns from the
database. There are 3 filters, one is location, and the other 2 are
dates, a from and a to. When the page first loads it will show no
records, but when submitted to itself it will filter the records returned.

I am using ASP with VBScript. My database is SQL 2000 server.

I have a Stored Procedure:
ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
-- Add the parameters for the stored procedure here
@Location varchar(7),
@From datetime,
@To datetime
AS
BEGIN
SELECT TOP 100 PERCENT dbo.purchaseorder.poh_order AS PONumber,
dbo.purchaseorder.poh_vendor + ' ' + dbo.vendor.vnd_name AS Vendor,
dbo.purchaseorder.poh_buyer AS Buyer,
dbo.purchaseorder.poh_orderdate AS OrderDate
FROM dbo.plannerbuyer RIGHT OUTER JOIN
dbo.purchaseorder INNER JOIN
dbo.vendor ON dbo.purchaseorder.poh_vendor =
dbo.vendor.vnd_vendor ON
dbo.plannerbuyer.plb_plannerbuyer =
dbo.purchaseorder.poh_buyer
WHERE (dbo.purchaseorder.poh_orderstatus = 'Open') AND
(dbo.plannerbuyer.plb_phonearea = CASE WHEN
@Location = 'Leeds' THEN '113' WHEN @Location = 'Wycombe' THEN '1494'
WHEN @Location = 'Both' THEN '%' END) AND
dbo.purchaseorder.poh_orderdate BETWEEN @From AND @To
ORDER BY OrderDate, PONumber
END

It takes 3 inputs from an ASP page, location, from date and to date. As
the last 2 are using datetime as the format, and the columns are
datatime, I assumed (from reading posts here) that I should be using the
ISO standard for date. Is this assumption correct?

To test my SP I did this:
EXEC OutstandingPurchaseOrderReport 'Wycombe','01/01/2001','01/01/2005'

My results:
1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12 00:00:00.000
1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16 00:00:00.000

I also tried this EXEC OutstandingPurchaseOrderReport
'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'

And got exactly the same results. I am using Server Management Studio
Express, I don't know if that makes any difference.

Now on to the page itself:

<%
Dim ospo__Location
ospo__Location = "Both"
If (Request.Form("Location") <> "") Then
ospo__Location = Request.Form("Location")
End If
%>
<%
Dim ospo__From
ospo__From = "01/01/2000"
If (Request.Form("From") <> "") Then
ospo__From = Request.Form("From")
End If
%>
<%
Dim ospo__To
ospo__To = "01/01/2010"
If (Request.Form("To") <> "") Then
ospo__To = Request.Form("To")
End If
%>
<%
Dim ospo
Dim ospo_cmd
Dim ospo_numRows

Set ospo_cmd = Server.CreateObject ("ADODB.Command")
ospo_cmd.ActiveConnection = MM_aclv4v2_STRING
ospo_cmd.CommandText = "{call dbutstandingPurchaseOrderReport(?,?,?)}"
ospo_cmd.Prepared = true
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
255, ospo__Location) ' adVarChar
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
-1, ospo__From) ' adDBTimeStamp
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
-1, ospo__To) ' adDBTimeStamp

Set ospo = ospo_cmd.Execute
ospo_numRows = 0
%>

When the page first loads it shows no records. I wanted to show all
records first, which is why I gave the dates presets. When I entered the
ISO date I got this error:
Application uses a value of the wrong type for the current operation.

If I manually enter the date on the page like this: 25/12/2004 then I
get the results that I expect.

What I don't understand is why the ISO dates don't work when I enter
them in to the form. Everywhere I read says I need to enter it in ISO.
The plan was to have a pop up date picker that returned the data in ISO,
but if the page won't accept ISO I will use the UK format date.

The page is for one person to use on an intranet. But I want to
understand the date in ASP and SQL, as its really doing my head in.

Thank you in advance to anyone who reads this monster post and can help me!

Steve

 

[Bob Barrows [MVP]]

I have been trying to follow the recent posts about the date format. I
am building a single page report, which displays 4 columns from the
database. There are 3 filters, one is location, and the other 2 are
dates, a from and a to. When the page first loads it will show no
records, but when submitted to itself it will filter the records
returned.

I am using ASP with VBScript. My database is SQL 2000 server.

I have a Stored Procedure:
ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
-- Add the parameters for the stored procedure here
@Location varchar(7),
@From datetime,
@To datetime

This is the only part you need to show us. We don't need to see the rest

It takes 3 inputs from an ASP page, location, from date and to date.
As the last 2 are using datetime as the format, and the columns are
datatime, I assumed (from reading posts here) that I should be using
the ISO standard for date. Is this assumption correct?

Yes. It is guaranteed not to be misinterpreted

To test my SP I did this:
EXEC OutstandingPurchaseOrderReport
'Wycombe','01/01/2001','01/01/2005'

Hardly a fair test ... 01/01 can hardly be misinterpreted, now can it?

My results:
1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12
00:00:00.000 1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16
00:00:00.000

I also tried this EXEC OutstandingPurchaseOrderReport
'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'

And got exactly the same results. I am using Server Management Studio
Express, I don't know if that makes any difference.

No difference at all.. The locale used in the database can make the
non-ISO format problematical. The default installation of SQL Server
results in the US locale being used.

Now on to the page itself:

<%

Set ospo_cmd = Server.CreateObject ("ADODB.Command")
ospo_cmd.ActiveConnection = MM_aclv4v2_STRING

Bad, very bad, extremely bad practice (did I say this was a bad
practice?)

Never set an object's ActiveConnection property to a string. Doing so
causes an implicit connection to be created behind the scenes. What's
wrong with that? Well:
1. You have no control over it
2. Doing so can consume extra resources and kill performance because
you may be circumventing connection pooling.

ALWAYS create an explicit connection object:

set cn = createobject("adodb.connection")
cn.open MM_aclv4v2_STRING 'Ugghhh! Macromedia :-b

and use that connection object whenever a connection object is required
in your page (which may be multiple times), like this:

Set ospo_cmd.ActiveConnection = cn

With an explicit connection variable, you can control when it gets
closed (freeing up the connection to be returned to the pool), thus
helping your server and database conserve resources.

ospo_cmd.CommandText = "{call
dbutstandingPurchaseOrderReport(?,?,?)}"

The odbc parameter syntax is not needed.Simply do this:
ospo_cmd.CommandText ="dbutstandingPurchaseOrderReport"
ospo_cmd.CommandType = 4 'adCmdStoredProc

ospo_cmd.Prepared = true

Again, very rarely needed.

ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
255, ospo__Location) ' adVarChar
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
-1, ospo__From) ' adDBTimeStamp
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
-1, ospo__To) ' adDBTimeStamp

Nothing wrong with doing this, but you are going to entirely too much
trouble!
The problem here is: ospo__From and ospo__To are still strings!! You
should convert them to dates before passing them to the procedure.

It appears to me that, unless these values are coming from calendar
controls in your client-side form, you have not been following this
thread at all. Please clarify that you have taken steps to make sure the
user is either selecting a date from a calendar control or that you are
using client-side code to build the dates being passed from selections
made in year,month and day dropdowns. Do not depend on the user
correctly entering a date in a textvox!

If you are using a calendar control, simply replace ospo__From in the
CreateParameter call with CDate(ospo__From)

Set ospo = ospo_cmd.Execute

Again, you are going to too much trouble here. There are no output
parameters and you have no interest in the return parameter, so an
explicit Command object is not needed. Do this instead:
Set ospo = createobject("adodb.recordset")
cn.OutstandingPurchaseOrderReport ospo__Location, _
CDate(ospo__From), CDate(ospo__To), ospo

 

[Bob Barrows [MVP]]

I have been trying to follow the recent posts about the date format. I
am building a single page report, which displays 4 columns from the
database. There are 3 filters, one is location, and the other 2 are
dates, a from and a to. When the page first loads it will show no
records, but when submitted to itself it will filter the records
returned.

It takes 3 inputs from an ASP page, location, from date and to date.
As the last 2 are using datetime as the format, and the columns are
datatime, I assumed (from reading posts here) that I should be using
the ISO standard for date. Is this assumption correct?

I have to clarifuy this:
If you are using dynamic sql (ugh!!!) to construct a sql statement, then
date literals used in the string should indeed be in ISO format
(yyyymmdd)

If you are passing parameters, as you are doing in your attempt, you
need to pass values that have been explicitly converted to the proper
datatypes.

 

[Dooza]

Hi Bob,
You guessed right, I am using Dreamweaver to construct my code, but am
trying to work out what it does, so your comments are invaluable to me.
I am a visual designer, but I am still learning how it all works.

I am currently trying to implement a date picker, the one I first used
only gave me US format, which wasn't what I wanted, so I am now trying
one with ISO format.

I confirmed in both the SP via SQL Server Management Studio and on the
ASP page that the server is using UK format dates. Is it the local of
the server and my installation that is making it do this?

I did have a play with CDate but it didn't work, once I get the date
picker to work I will use it again.

Steve

I have been trying to follow the recent posts about the date format. I
am building a single page report, which displays 4 columns from the
database. There are 3 filters, one is location, and the other 2 are
dates, a from and a to. When the page first loads it will show no
records, but when submitted to itself it will filter the records
returned.

I am using ASP with VBScript. My database is SQL 2000 server.

I have a Stored Procedure:
ALTER PROCEDURE [dbo].[OutstandingPurchaseOrderReport]
-- Add the parameters for the stored procedure here
@Location varchar(7),
@From datetime,
@To datetime

This is the only part you need to show us. We don't need to see the rest

It takes 3 inputs from an ASP page, location, from date and to date.
As the last 2 are using datetime as the format, and the columns are
datatime, I assumed (from reading posts here) that I should be using
the ISO standard for date. Is this assumption correct?

Yes. It is guaranteed not to be misinterpreted

To test my SP I did this:
EXEC OutstandingPurchaseOrderReport
'Wycombe','01/01/2001','01/01/2005'

Hardly a fair test ... 01/01 can hardly be misinterpreted, now can it?

My results:
1230002864 RAP107 Rapid Electronics Limited ANDYB 2004-02-12
00:00:00.000 1230008306 HIG1 High End Systems Inc CALLYB 2004-09-16
00:00:00.000

I also tried this EXEC OutstandingPurchaseOrderReport
'Wycombe','2004-02-12 00:00:00.000','2004-02-12 00:00:00.000'

And got exactly the same results. I am using Server Management Studio
Express, I don't know if that makes any difference.

No difference at all.. The locale used in the database can make the
non-ISO format problematical. The default installation of SQL Server
results in the US locale being used.

Now on to the page itself:

<%

Set ospo_cmd = Server.CreateObject ("ADODB.Command")
ospo_cmd.ActiveConnection = MM_aclv4v2_STRING

Bad, very bad, extremely bad practice (did I say this was a bad
practice?)

Never set an object's ActiveConnection property to a string. Doing so
causes an implicit connection to be created behind the scenes. What's
wrong with that? Well:
1. You have no control over it
2. Doing so can consume extra resources and kill performance because
you may be circumventing connection pooling.

ALWAYS create an explicit connection object:

set cn = createobject("adodb.connection")
cn.open MM_aclv4v2_STRING 'Ugghhh! Macromedia :-b

and use that connection object whenever a connection object is required
in your page (which may be multiple times), like this:

Set ospo_cmd.ActiveConnection = cn

With an explicit connection variable, you can control when it gets
closed (freeing up the connection to be returned to the pool), thus
helping your server and database conserve resources.

ospo_cmd.CommandText = "{call
dbutstandingPurchaseOrderReport(?,?,?)}"

The odbc parameter syntax is not needed.Simply do this:
ospo_cmd.CommandText ="dbutstandingPurchaseOrderReport"
ospo_cmd.CommandType = 4 'adCmdStoredProc

ospo_cmd.Prepared = true

Again, very rarely needed.

ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param1", 200, 1,
255, ospo__Location) ' adVarChar
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param2", 135, 1,
-1, ospo__From) ' adDBTimeStamp
ospo_cmd.Parameters.Append ospo_cmd.CreateParameter("param3", 135, 1,
-1, ospo__To) ' adDBTimeStamp

Nothing wrong with doing this, but you are going to entirely too much
trouble!
The problem here is: ospo__From and ospo__To are still strings!! You
should convert them to dates before passing them to the procedure.

It appears to me that, unless these values are coming from calendar
controls in your client-side form, you have not been following this
thread at all. Please clarify that you have taken steps to make sure the
user is either selecting a date from a calendar control or that you are
using client-side code to build the dates being passed from selections
made in year,month and day dropdowns. Do not depend on the user
correctly entering a date in a textvox!

If you are using a calendar control, simply replace ospo__From in the
CreateParameter call with CDate(ospo__From)

Set ospo = ospo_cmd.Execute

Again, you are going to too much trouble here. There are no output
parameters and you have no interest in the return parameter, so an
explicit Command object is not needed. Do this instead:
Set ospo = createobject("adodb.recordset")
cn.OutstandingPurchaseOrderReport ospo__Location, _
CDate(ospo__From), CDate(ospo__To), ospo

 

[Bob Barrows [MVP]]

Hi Bob,
You guessed right, I am using Dreamweaver to construct my code, but am
trying to work out what it does, so your comments are invaluable to
me. I am a visual designer, but I am still learning how it all works.

I am currently trying to implement a date picker, the one I first used
only gave me US format, which wasn't what I wanted, so I am now trying
one with ISO format.

You should not care what format the datepicker is providing (something
is puzzling me here: every datepicker I've ever seen has allowed
configurable output formats - doesn't yours?). You can format the dates
any way you want when you display them to the user. What you need to
guarantee is that a consistent format is being used to pass the dates to
the server. The datepicker can do this. Use it. Don't worry about it not
giving you dates in ISO format (see my other message). Since the string
provided by the datepicker will contain a date with a consistent format,
it will be child's play to parse the year month and day values from it
to construct a proper date variable using dateserial()

I confirmed in both the SP via SQL Server Management Studio and on the
ASP page that the server is using UK format dates. Is it the local of
the server and my installation that is making it do this?

No, it is the fact that you are not passing actual dates to the
procedures (you are passing strings). The strings being passed from the
client need to be converted to dates before being passed to the
procedure. The best practice is to pass year, month and day values to
the server so your code can use dateserial(year,month,day) to create
actual date variables. You could parse the strings currently being
passed to extract the year, month and day values, but this is prone to
errors. Do not depend on user input having any expected format. Make
your application format-independant. Instead of having the user enter a
formatted date, use a calendar control, or 3 dropdowns, or at least, 3
textboxes to allow the user to enter a year, month and day

This is part of the value of using parameters instead of dynamic sql, by
the way.

 

[Dooza]

You should not care what format the datepicker is providing (something
is puzzling me here: every datepicker I've ever seen has allowed
configurable output formats - doesn't yours?). You can format the dates
any way you want when you display them to the user. What you need to
guarantee is that a consistent format is being used to pass the dates to
the server. The datepicker can do this. Use it. Don't worry about it not
giving you dates in ISO format (see my other message). Since the string
provided by the datepicker will contain a date with a consistent format,
it will be child's play to parse the year month and day values from it
to construct a proper date variable using dateserial()

The first one only output in US format, I spoke to the author, he
couldn't care less as it was an old tutorial. I moved on, and have found
one that allows the date picker to use ISO standard date. Its working
nicely now. I am using CData in the parameters as you suggested.

No, it is the fact that you are not passing actual dates to the
procedures (you are passing strings). The strings being passed from the
client need to be converted to dates before being passed to the
procedure. The best practice is to pass year, month and day values to
the server so your code can use dateserial(year,month,day) to create
actual date variables. You could parse the strings currently being
passed to extract the year, month and day values, but this is prone to
errors. Do not depend on user input having any expected format. Make
your application format-independant. Instead of having the user enter a
formatted date, use a calendar control, or 3 dropdowns, or at least, 3
textboxes to allow the user to enter a year, month and day

I had seen DateSerial, and am now using that for the preset variables,
its working nicely now.

The date picker is working fine.

This is part of the value of using parameters instead of dynamic sql, by
the way.

I don't use dynamic sql, I know how bad it is, and was never taught to
use it. My SP is just a SELECT statement using CASE in the WHERE...does
that make it dynamic SQL?

Thank you for your comments Bob, whilst I am very dependant on
Dreamweaver, I am learning ASP and from your advice, I am learning how
to use it correctly.

Thank you!

Steve

 

[Bob Barrows [MVP]]

I don't use dynamic sql, I know how bad it is, and was never taught to
use it. My SP is just a SELECT statement using CASE in the
WHERE...does that make it dynamic SQL?

No. Dynamic sql is where you use concatenation to construct a string
containing a sql statement. While it is possible for a stored procedure
to do this:

declare @sql varchar(1000)
set @sql = 'select ...where col1=' + @parmvalue
exec(@sql)

That is not what your procedure is doing.