SQL Server 2000: inserting date problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Server 2000: inserting date problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-11-23 : 10:57:32

Jill writes "Hi

I'm experiencing a problem when inserting a date value into an sql server database. I realise what the problem is, i.e. sql server is recognising a decimal value and not a date value.

I've tried converting the value to date using the cdate function and the datevalue function and both functions are not working.

Do you perhaps know why this is the case? Please Help.

Jill"

azbatuk
Starting Member

19 Posts

Posted - 2001-11-25 : 17:12:42

AjarnMark,

I have the same problem.
My situation is this:
Server : SQL Server2000
Field : DateTime
The error message:
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SQL statement
INSERT INTO members (username, description, registerdate)
VALUES ('username', 'test account', '11/21/2001')

SQL execution
Conn.Execute(SqlStatement)

It doesn't work !!!

nr
SQLTeam MVY

12543 Posts

Posted - 2001-11-25 : 20:50:04

try
INSERT INTO members (username, description, registerdate)
VALUES ('username', 'test account', '20012111')

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

azbatuk
Starting Member

19 Posts

Posted - 2001-11-26 : 11:53:21

nr,

I tried your suggestion.
I also tried this: VALUES ('username', 'test account', convert(datetime,'11/21/2001'))

Both didn't work. The error message is the same:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
May this be a problem with my server itself?
By the way my SQL Server 2000 is a Trial version. I installed SP1.

Another thing;
I noticed your signature. "Cursors are useful if you don't know sql."
Does this mean we don't need to declare any cursor type when we use SQL commands like SELECT, INSERT, UPDATE ?
What about lock types ?
I am struggling to learn SQL and try to stay away from Recordset object and Cursor and Lock types.
Am I doing something good ? :)

quote:

try
INSERT INTO members (username, description, registerdate)
VALUES ('username', 'test account', '20012111')

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2001-11-26 : 12:19:45

You might want to try appending a time to the date:

'11/21/01 12:12:00'

You are wise to avoid cursors, and you certainly don't need to declare them when using any select, insert or update statements.

As for lock types, you can control them in stored procedures by using commands in the select statement or setting the isolation level. Here's an article:

[url]http://www.4guysfromrolla.com/webtech/101100-1.shtml[/url]

I wouldn't normally suggest doing something like the - especially for beginners. Usually the defaults will be OK.

Edited by - andre on 11/26/2001 12:25:42

azbatuk
Starting Member

19 Posts

Posted - 2001-11-26 : 14:01:02

Andre,

It didn't work either.

Now, this must be something with the database or server I think.

What I will try is this;

I'll delete the table and recreate it; if it doesn't work I'll do the same with the database and then SQL server itself.

Thanks for help

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-26 : 14:24:35

My guess is that your server's regional settings are interpreting 11/21/2001 as DD/MM/YYYY instead of MM/DD/YYYY format. Try using a YYYYMMDD format:

VALUES ('username', 'test account', convert(datetime,'20011121'))

azbatuk
Starting Member

19 Posts

Posted - 2001-11-26 : 15:54:33

robvolk,

You were right. I tried your solution and it worked.

Thank you for your help.
You saved my sanity. :)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-26 : 18:21:27

Azbatuk,

Regarding your tangential question on Cursors, I believe nr's signature line is in regard to SQL cursors (i.e. DECLARE CURSOR FOR...) which really hamstrings SQL Server's performance and is a typical fallback method for people who don't have sufficient knowledge of SQL technique. However, it sounded to me like you were talking about the ADO settings for cursor type (e.g. adOpenForwardOnly, adOpenStatic, etc.) and lock type (e.g. adLockReadOnly, adLockOptimistic, etc.). I would encourage you to definitely assign these values in your ASP code. The defaults work in many situations, but I encourage explicit assignment.

-------------------
It's a SQL thing...

azbatuk
Starting Member

19 Posts

Posted - 2001-11-27 : 10:02:30

AjarnMark,

I read your advice very carefully.
Yes, I was talking about ADO recordset object and Cursor and Lock types.
I am new to SQL and trying to learn. I don't have a clear idea on these Cursor and Lock types with regard to SQL queries. I think I do know enough when it is ADO recordset object.

What I do nowadays within my ASP pages is this:

SELECT statement example;
Sql = "SELECT * FROM members INNER JOIN settings ON members.memberid = settings.memberid WHERE members.memberid=" & Request("memberid")
Set RS = Conn.Execute(Sql)

UPDATE statement example;
Sql = "UPDATE members SET "
Sql = Sql & "email='" & email & "'"
Sql = Sql & ", siteurl='" & siteurl & "'"
Sql = Sql & " WHERE memberid=" & Request("memberid")
Conn.Execute(Sql)

INSERT statement example;
Sql="INSERT INTO members (membername, email, registerdate) values("
Sql = Sql & "'" & membername & "'"
Sql = Sql & ",'" & email & "'"
Sql = Sql & ",'" & date() & "'"
Conn.Execute(Sql)

As you can see, I don't use ADO Recordset object and there is no Cursor and Lock types.

What I want to know is if this is the right method or not ?

Imagine, you have an ASP page that receives 200,000 views a day, and there are SELECT, UPDATE and INSERTs in this page, and most of these queries are on the same table. Imagine a top websites list, for example, with 2,000 member websites with an average of 100 pageviews a day. And your ASP page is counting every hit they receive and checking certain conditions, writing hit counts in your SQL server and then constructing a top list.

I am actually developing (or rather trying to develop) such an application for experimental purposes.

All help is greately appreciated. :)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-28 : 15:32:00

azbatuk,

I don't know that there is anything wrong with doing Conn.Execute(sql) type of work in ASP, I just have never used it. I prefer to do all of my inserts and updates in SQL Server stored procedures because they are faster (compiled vs. interpreted) and more secure (I don't grant SQL INSERT/UPDATE/DELETE permissions on tables to users). Typically, this means that I am creating and ADO Command object to handle passing parameters and executing the stored procedure.

So far this method has worked well for me, and met all of our stress-test needs. But, I haven't done any stress-test comparisons with different programming methods.

-------------------
It's a SQL thing...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources