Insert Date and Time in SQL Server 2000 using ASP

[Robin Lawrie]

Hi again, another problem!

I've moved from an Access database to SQL server and am now having trouble
inserting dates and times into seperate fields. I'm using ASP and the code
below to get the date and time, but my script is erroring.

'-- Get login date and time
cmdLoginDate = Date()
cmdLoginTime = Time()

'-- Insert into table
cmdDC1.CommandText = "INSERT INTO tblUserTracking (LoginDate, LoginTime,
Username) VALUES ('" & cmdLoginDate & "','" & cmdLoginTime & "','" &
cmdUsername & "') "

'-- Error message is:

The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.

Can anyone help me fix this....I've looked on the web but all the articles
I've found explain why there are problems but I've not found anything that
helps me fix the problem.....

Thanks in advance....

Robin.

 

[Bob Barrows [MVP]]

Hi again, another problem!

I've moved from an Access database to SQL server and am now having
trouble inserting dates and times into seperate fields. I'm using ASP
and the code below to get the date and time, but my script is
erroring.

'-- Get login date and time
cmdLoginDate = Date()
cmdLoginTime = Time()

'-- Insert into table
cmdDC1.CommandText = "INSERT INTO tblUserTracking (LoginDate,
LoginTime, Username) VALUES ('" & cmdLoginDate & "','" &
cmdLoginTime & "','" & cmdUsername & "') "

'-- Error message is:

The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

Can anyone help me fix this....I've looked on the web but all the
articles I've found explain why there are problems but I've not found
anything that helps me fix the problem.....

Thanks in advance....

Robin.

As always, sql statements cannot ve debugged without knowing what they are.
Response.Write the result of your concatenation. It is always best to store
the result of your concatenation into a variable rather than setting it
directly to the property of your ado object. It is easy to response-write a
variable.

That said, i would advise not doing the concatenation at all. Use a stored
procedure and pass parameter values to it (no Command object needed):

cn.proc_name cmdLoginDate, cmdLoginTime, cmdUsername

If using stored procedures is against your religion or something, you can
still utilize parameters. It looks like cmdDC1 is a Command object, so do
this:

cmdDC1.CommandText = "INSERT INTO tblUserTracking " & _
"(LoginDate, LoginTime,Username) VALUES (?,?,?) "
Set cmdDC1.activeconnection=cn
cmdDC1.Execute , _
array(cmdLoginDate, cmdLoginTime, cmdUsername), 129

If this results in an error, response-write your parameter values. Show us
what they are and provide us with the DDL for yout table
(www.aspfaq.com/5006)

Bob Barrows

 

[Aaron [SQL Server MVP]]

Can I ask why you are inserting the date and time into two separate columns?
SQL Server doesn't have the ability to separate date and time. You will
notice that if you do this as written, LoginDate will have the date AND a
time value of midnight, and LoginTime will have the time AND a date value of
1900-01-01. You should combine these two columns into a single column. I
can see in some cases that a separate column for just the date might make
sense, but time by itself will be pretty useless. How about this design, in
that case:

CREATE TABLE dbo.UserTracking
(
Username VARCHAR(32) NOT NULL,
LoginDateTime SMALLDATETIME NOT NULL DEFAULT GETDATE(),
LoginDate AS CONVERT
(
SMALLDATETIME,
DATEADD(DAY, DATEDIFF(DAY, 0, LoginDate))
)
)

Notice I renamed your table. The tbl prefix serves little purpose except to
increase the amount of typing required...

Anyway, now you can leave the database to insert the current date/time into
the table, and your SQL statement becomes much tidier.

cmdDC1.CommandText = 'INSERT dbo.UserTracking(UserName) SELECT '" &
cmdUserName & "'"

Don't forget to remove any single quotes from cmdUserName, and please
consider using stored procedures.

http://www.aspfaq.com/2201

 

[Robin Lawrie]

Thanks again for your help Bob, it is very much appreciated!

I'm new to SQL Server and am trying to develop a web application. I don't
know if my hosting company allows stored procedures to be created on their
shared SQL Server hosting plan but I will ask. If so, I'll look into using
stored procedures if the hosting company offers it (and if they do offer it
you can be sure of new posts to this newsgroup!)

Regards,

Robin.

 

[Robin Lawrie]

Hi Aaron,

Shortly after I posted this question I thought the exact same thing you are
asking - why do I need to split the date and time. The answer is I don't
need to! I'm converting from an Access DB to SQL Server and the Access DB
split the date and time so I was trying to do the same thing in SQL Server.

Thank you very much for taking the time to reply to post in such detail and
with such good examples and clear explanations. As I said in my reply to
Bob, I will look into using stored procedures if my hosting company allows
me to use them on their shared SQL Server.

Regards,

Robin.

 

[Bob Barrows [MVP]]

Thanks again for your help Bob, it is very much appreciated!

I'm new to SQL Server and am trying to develop a web application. I
don't know if my hosting company allows stored procedures to be
created on their shared SQL Server hosting plan but I will ask. If
so, I'll look into using stored procedures if the hosting company
offers it (and if they do offer it you can be sure of new posts to
this newsgroup!)

Here is my canned reply for running stored procedures from ASP:
http://tinyurl.com/jyy0

Bob Barrows

 

[Dave Anderson]

cmdLoginDate = Date()
cmdLoginTime = Time()

'-- Insert into table
cmdDC1.CommandText = "INSERT INTO tblUserTracking (LoginDate,
LoginTime, Username) VALUES ('" & cmdLoginDate & "','" &
cmdLoginTime & "','" & cmdUsername & "') " ...

In addition to the other suggestions here, consider using a default value of
(getdate()) on the column, and forget passing it altogether. In other words,
let SQL Server generate the date.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

In addition to the other suggestions here, consider using a default value
of

(getdate()) on the column, and forget passing it altogether.

Hey, I did suggest that.

 

[Mark Schupp]

on a related topic I often see the recommendation to use a command object
and parameters instead of dynamic SQL (so far I refuse to write stored
procedures for SQL Server, Access, AND Oracle).

Any performance trade-offs between the two methods?

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

 

[Dave Anderson]

Hey, I did suggest that.

So you did. My oversight.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

on a related topic I often see the recommendation to use a command object

and parameters instead of dynamic SQL (so far I refuse to write stored
procedures for SQL Server, Access, AND Oracle).

The preferred order, in general, is:

- stored procedure with command object
- stored procedure with connection object
- dynamic SQL

I usually bypass #1 in favor of #2, because I find the syntax much more
intuitive, and debugging easier. I usually only use #1 if I require access
to output/return values. If there were other items I could inject between
#2 and #3, I would.

What is the basis of your refusal to use stored procedures? Do you have
applications that actively communicate with SQL Server, Access and Oracle
simultaneously? Even if that's the case, doesn't it make more sense to
encapsulate the provider-specific code within each database, than to store
all those ad hoc queries (likely with provider-specific extensions anyway)
in your application code?

A

 

[Dave Anderson]

on a related topic I often see the recommendation to use a command
object and parameters instead of dynamic SQL (so far I refuse to
write stored procedures for SQL Server, Access, AND Oracle).

Any performance trade-offs between the two methods?

By two methods, do you mean

dynamic SQL -vs- stored procedured with Command Object
or
dynamic SQL -vs- stored procedured without Command Object
or
stored procedure with-vs-without Command Object?


I am told by my personal SQL Server DBA (sheepishly, in fact) that despite
conventional wisdom, in real-world application, dynamic SQL performs
slightly better than stored procedures. That said, I still recommend against
dynamic SQL execution for a wide variety of reasons. A bunch of security
concerns top the list, but organization, reusability, and modularity are
important as well.

Getting back to performance, consider the problem of SQL injection. It is
more costly (though on the web server rather the DB server) to protect
yourself from SQL injection where dynamic SQL is allowed than where it is
not (SP+Command Object or SP-as-method-of-connection, for example). So this
one security concern already starts to reclaim some (if not all) of the
performance gain you MAY see from dynamic SQL execution.

In any case, the performance difference is very small, and should not be the
deciding factor.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Aaron [SQL Server MVP]]

I am told by my personal SQL Server DBA (sheepishly, in fact) that despite

conventional wisdom, in real-world application, dynamic SQL performs
slightly better than stored procedures.

Has he proven this to you? I've seen isolated cases of this (usually due to
badly out-of-date statistics r a horrible cached plan) but it is the
exception rather than the norm.

That said, I still recommend against
dynamic SQL execution for a wide variety of reasons. A bunch of security
concerns top the list, but organization, reusability, and modularity are
important as well.

Absolutely.

A

 

[Bob Barrows [MVP]]

on a related topic I often see the recommendation to use a command
object and parameters instead of dynamic SQL (so far I refuse to
write stored procedures for SQL Server, Access, AND Oracle).

Any performance trade-offs between the two methods?

There is no performance trade-off. Using the command object to pass
parameters to your sql string is more secure than using concatenation. It is
also easier than concatenation, unless you code all the CreateParameter
statements yourself, which I have demonstrated is not necessary. Using the
parameter array argument in the command's execute statement allows the
parameter values to be passed without creating the Parameters collection.

Bob Barrows

 

[Dave Anderson]

Has he proven this to you? I've seen isolated cases of this (usually
due to badly out-of-date statistics r a horrible cached plan) but it
is the exception rather than the norm.

SHE points out that this is more common than you suggest. One source:

"When a stored procedure is first executed (and it does not have
the WITH RECOMPILE option), it is optimized and a query plan is
compiled and cached in SQL Server's buffer. If the same stored
procedure is called again from the same connection, it will used
the cached query plan instead of creating a new one, often saving
time and boosting performance. This may or may not be what you
want.

If the query in the stored procedure is exactly the same each
time, and the query plan is the same each time, then this is a
good thing. But if the query within the stored procedure is
dynamic (for example, the WHERE clauses changes from one
execution of the stored procedure to the next), then this may not
be a good thing, as the query may not be optimized when it is
run, and the performance of the query can suffer greatly. This
can happen because changes in the query plan may occur, and if
you run an incorrect cached query plan for what is essentially a
new query, it may not be appropriate and it may cause performance
to suffer greatly.

If you know that your query's query plan will vary each time it is
run from a stored procedure, you will want to add the WITH
RECOMPILE option when you create the stored procedure. This will
force the stored procedure to be re-compiled each time it is run,
ensuring the query is optimized with the correct query plan each
time it is run. Yes, this will circumvent the reuse of cached
query plans, hurting performance a little, but it is more desirable
than reusing incorrect query plans."

http://www.sql-server-performance.com/stored_procedures.asp


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.

 

[Mark Schupp]

What is the basis of your refusal to use stored procedures? Do you have

applications that actively communicate with SQL Server, Access and Oracle
simultaneously? Even if that's the case, doesn't it make more sense to
encapsulate the provider-specific code within each database, than to store
all those ad hoc queries (likely with provider-specific extensions anyway)
in your application code?

Call it laziness if you must (I prefer to call it "refusal to deal with
Oracle any more than necessary") but we have a single application that works
unchanged against Access, Oracle, and SQLServer (there is automated logic in
the "statement building" process to deal with the different SQL dialects).
The effort to code the SQL logic into 3 separate sets of stored procedures
(and modify them for upgrades) is more than I am willing to accept at this
point in time.

Although I believe we are creating the SQL statements safely I am concerned
about SQL Injection and am considering gradually migrating to parameterized
command objects for future upgrades so I was wondering if there were any
performance "gotchas".

--
Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com

 

[Aaron [SQL Server MVP]]

SHE points out

Whoops!

If the query in the stored procedure is exactly the same each
time, and the query plan is the same each time, then this is a
good thing. But if the query within the stored procedure is
dynamic (for example, the WHERE clauses changes from one
execution of the stored procedure to the next), then this may not
be a good thing, as the query may not be optimized when it is
run, and the performance of the query can suffer greatly. This
can happen because changes in the query plan may occur, and if
you run an incorrect cached query plan for what is essentially a
new query, it may not be appropriate and it may cause performance
to suffer greatly.

If you know that your query's query plan will vary each time it is
run from a stored procedure, you will want to add the WITH
RECOMPILE option when you create the stored procedure. This will
force the stored procedure to be re-compiled each time it is run,
ensuring the query is optimized with the correct query plan each
time it is run. Yes, this will circumvent the reuse of cached
query plans, hurting performance a little, but it is more desirable
than reusing incorrect query plans."

http://www.sql-server-performance.com/stored_procedures.asp

There are some statements in there that are true, there are some statements
that are not necessarily true, and there are some statements that are false
most of the time.

For example, it is difficult to determine whether the stored procedure will
be called more often than not with the same parameters (or at least dfferent
parameters that will yield the same execution plan). This comes through in
TESTING, and cannot be solved simply by throwing on WITH RECOMPILE. On
systems I have inherited, I have solved several performance issues merely by
*removing* WITH RECOMPILE. Maybe the previous db people read that article
and assumed it was gospel. Another point is that the WHERE clause changes
will have minimal effect if the table is properly indexed. If the WHERE
clause uses an index then different values shouldn't have any effect on
performance. If the WHERE clause uses different columns depending on the
parameters, then this is a different issue, and I don't think you will get a
decent plan no matter what you do.

FYI, the web site you reference doesn't have a very good track record among
SQL Server professionals. Some don't even read the web site at all, unless
it is to scoff. Some of the articles are half-decent, but I have seen
several examples of bad information being passed on to readers. Not that I
am completely innocent of that myself, but it justshows that testing and
optimizing in your own environment is far more valuable than some article
thrown onto a web site.

A

 

[Michael D. Kersey]

on a related topic I often see the recommendation to use a command object
and parameters instead of dynamic SQL (so far I refuse to write stored
procedures for SQL Server, Access, AND Oracle).

Any performance trade-offs between the two methods?

Within the Microsoft world, the relative performance of stored
procedures (SPs) versus dynamic SQL depends on the version of SQL Server
used. Versions prior to SQL Server 2000 precompiled stored procedures.
In contrast SQL Server 2000 stored procedures are not precompiled:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_7cmm.asp

There's also a possible performance hit for lengthy dynamic SQL
statements which must be transferred over the network.

But there's more than just performance involved in the decision whether
to use SPs or not. Here's another good discussion of the issue of SPs
versus dynamic SQL:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx

With the advent of SQL Server 2000 stored procedures lose their
performance advantage mostly, but there are plenty of very good reasons
(security, maintenance) for using them.

 

[Mark Schupp]

That's what I was after, thanks.

--
--Mark Schupp
Head of Development
Integrity eLearning
www.ielearning.com