I dont understand :(

I dont understand :(

Post by Robb Mead » Wed, 19 Mar 2003 04:48:43



Hi,

I have again this evening attempted to port across my web application to my
clients server (the SQL stuff)...

I now have all the tables and views and sp's set up...

I ran the application and I am currently getting an error :

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[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.

E:\WEBUSERS\WWW.PARASOLIT2.CO.UK\ADMIN\../_IncludeScripts/ReadWrite.asp,
line 16

What I dont understand is why this works FINE without error on my server
with SQL Server 2000 and SP3 installed (same as the hosting company)....and
even last night on SQL Server 7 SP2 it got passed this area!!!

The ASP code causing the error is this line :

SQLInsert = "UPDATE tblLogon "
SQLInsert = SQLInsert & "Set InvalidAttempts = '0', DateLastLoggedIn = '" &
Year(Date) & "-" & Month(Date) & "-" & Day(Date) & " " & Time & "' "
SQLInsert = SQLInsert & "WHERE UserID = '" & Session("UserID") & "'"

Which produces the following SQL Statement :

UPDATE tblLogon Set InvalidAttempts = '0', DateLastLoggedIn = '2003-3-17
19:47:16' WHERE UserID = '1'

I have had LOADS of problems with date fields previously - but was reassured
that by entering the date in that format (yyyy-mm-dd) you avoid such
problems...

Can anyone shed any light on this? I was supposed to be spending the night
working on another section of the project but find myself now going round in
circles with stuff that previously worked :(

Thanks in advance for any/all help,

Regards

Robb Meae

 
 
 

I dont understand :(

Post by Robb Mead » Wed, 19 Mar 2003 04:50:58


Quote:> UPDATE tblLogon Set InvalidAttempts = '0', DateLastLoggedIn = '2003-3-17
> 19:47:16' WHERE UserID = '1'

Just ran the above in query analyser and got the same error - I then changed
the 17 and the 3 around in the date and it all worked fine - what the
f%&k...

What happened about this internationally recognised date format then :(

Whats my best move from here - as there are quite a few instances of where
I've coded it like this because of previous date problems...

Regards

Robb

 
 
 

I dont understand :(

Post by dw » Wed, 19 Mar 2003 05:29:25


Meade, you can try converting the date/time sent in as a parameter. I had
all kinds of headaches when the db wouldn't accept the date time being sent
in. I finally create a couple of lines of code to take the incoming date
parameter, convert it to an nvarchar (though I think varchar works too)
using the specific style. Try this,




that 112 is, lookup the convert function on SQL Server Books online.




Quote:> > UPDATE tblLogon Set InvalidAttempts = '0', DateLastLoggedIn = '2003-3-17
> > 19:47:16' WHERE UserID = '1'

> Just ran the above in query analyser and got the same error - I then
changed
> the 17 and the 3 around in the date and it all worked fine - what the
> f%&k...

> What happened about this internationally recognised date format then :(

> Whats my best move from here - as there are quite a few instances of where
> I've coded it like this because of previous date problems...

> Regards

> Robb

 
 
 

I dont understand :(

Post by Robb Mead » Wed, 19 Mar 2003 05:52:10


Quote:"dw" wrote...
> Meade, you can try converting the date/time sent in as a parameter. I had
> all kinds of headaches when the db wouldn't accept the date time being
sent
> in. I finally create a couple of lines of code to take the incoming date
> parameter, convert it to an nvarchar (though I think varchar works too)
> using the specific style. Try this,

Hi,

yeah, I saw the convert funciton - never used it to INSERT dates from ASP
before though - usually they go in fine - its pulling them out I've always
had the problems with before...

If I used a CONVERT function and specifiy a UK date/time number etc (103) -
will this have any affect on me pulling the data out later on using the same
CONVERT function?

ie, if I push it in using 103 - its not going to come out all mixed up again
is it?

Any further help appreciated..

Robb

 
 
 

I dont understand :(

Post by dw » Wed, 19 Mar 2003 07:04:45


Meade, I'm not sure. I'll look into it and let you know. There's always one
quick way to test it: Run your procedure in the SQL Analyzer, and pass it a
date that's in this format. Then query the table and see what you get back.


Quote:> "dw" wrote...

> > Meade, you can try converting the date/time sent in as a parameter. I
had
> > all kinds of headaches when the db wouldn't accept the date time being
> sent
> > in. I finally create a couple of lines of code to take the incoming date
> > parameter, convert it to an nvarchar (though I think varchar works too)
> > using the specific style. Try this,

> Hi,

> yeah, I saw the convert funciton - never used it to INSERT dates from ASP
> before though - usually they go in fine - its pulling them out I've always
> had the problems with before...

> If I used a CONVERT function and specifiy a UK date/time number etc
(103) -
> will this have any affect on me pulling the data out later on using the
same
> CONVERT function?

> ie, if I push it in using 103 - its not going to come out all mixed up
again
> is it?

> Any further help appreciated..

> Robb

 
 
 

I dont understand :(

Post by Michael MacGrego » Wed, 19 Mar 2003 07:10:01


Quote:> If I used a CONVERT function and specifiy a UK date/time number etc
(103) -
> will this have any affect on me pulling the data out later on using the
same
> CONVERT function?

Not as long as you storing it in a datetime datatype column. Dates are not
actually stored in any particular format, for full details see BOL (Books
OnLine).

BTW I think the international standard is yyyymmdd without the hyphens.

Michael MacGregor
Database Architect
SalesDriver

 
 
 

I dont understand :(

Post by Robb Mead » Wed, 19 Mar 2003 08:49:47


Seem to have it working with my insertings using the CONVERT function now -
so thanks for the help and info...

Until my next SQL problem .... hehe..

Regards

Robb

 
 
 

I dont understand :(

Post by Tibor Karasz » Wed, 19 Mar 2003 18:16:18


Robb,

Quote:> I have had LOADS of problems with date fields previously - but was reassured
> that by entering the date in that format (yyyy-mm-dd) you avoid such
> problems...

Someone gave you bad advice. The ANSI SQL "ISO" format is *not* safe in SQL Server. Use below
format instead:
YYYYMMDD

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


Quote:> Hi,

> I have again this evening attempted to port across my web application to my
> clients server (the SQL stuff)...

> I now have all the tables and views and sp's set up...

> I ran the application and I am currently getting an error :

> Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

> [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.

> E:\WEBUSERS\WWW.PARASOLIT2.CO.UK\ADMIN\../_IncludeScripts/ReadWrite.asp,
> line 16

> What I dont understand is why this works FINE without error on my server
> with SQL Server 2000 and SP3 installed (same as the hosting company)....and
> even last night on SQL Server 7 SP2 it got passed this area!!!

> The ASP code causing the error is this line :

> SQLInsert = "UPDATE tblLogon "
> SQLInsert = SQLInsert & "Set InvalidAttempts = '0', DateLastLoggedIn = '" &
> Year(Date) & "-" & Month(Date) & "-" & Day(Date) & " " & Time & "' "
> SQLInsert = SQLInsert & "WHERE UserID = '" & Session("UserID") & "'"

> Which produces the following SQL Statement :

> UPDATE tblLogon Set InvalidAttempts = '0', DateLastLoggedIn = '2003-3-17
> 19:47:16' WHERE UserID = '1'

> I have had LOADS of problems with date fields previously - but was reassured
> that by entering the date in that format (yyyy-mm-dd) you avoid such
> problems...

> Can anyone shed any light on this? I was supposed to be spending the night
> working on another section of the project but find myself now going round in
> circles with stuff that previously worked :(

> Thanks in advance for any/all help,

> Regards

> Robb Meae

 
 
 

I dont understand :(

Post by Robb Mead » Thu, 20 Mar 2003 05:47:31


"Tibor Karaszi" wrote ...

Quote:> Someone gave you bad advice. The ANSI SQL "ISO" format is *not* safe in

SQL Server. Use below

Quote:> format instead:
> YYYYMMDD

Hi Tibor,

Does that mean now that I'm using the CONVERT function on INSERT/UPDATE
statements that using the 102 for ANSI is a bad thing? Or should I be ok now
that I know I am always entering in a specific format, and then later
pulling out in the format I want etc?

Thanks in advance for any further help.

Regards

Robb

 
 
 

I dont understand :(

Post by Tibor Karasz » Fri, 21 Mar 2003 21:17:27


Robb,

Hard to say without seeing how you use the code. I prefer YYYYMMDD, as it is always safe. But
doing something like:

find that a bit more convoluted, though...

--
Tibor Karaszi, SQL Server MVP
For help on TSQL, please provide code we can execute in Query Analyzer


Quote:> "Tibor Karaszi" wrote ...

> > Someone gave you bad advice. The ANSI SQL "ISO" format is *not* safe in
> SQL Server. Use below
> > format instead:
> > YYYYMMDD

> Hi Tibor,

> Does that mean now that I'm using the CONVERT function on INSERT/UPDATE
> statements that using the 102 for ANSI is a bad thing? Or should I be ok now
> that I know I am always entering in a specific format, and then later
> pulling out in the format I want etc?

> Thanks in advance for any further help.

> Regards

> Robb

 
 
 

1. I dont understand the error

I keep getting the following error :

Msg 1105, Level 17, State 1
Can't allocate space for object '#tempmission________0000000595' in database
'tempdb' because the 'default' segment is full.
If you ran out of space in Syslogs, dump the transaction log.
Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of
the segment.

and I dont understand what to do to fix it.

2. Dirty Search Engine Tricks

3. dont understand dbcombo problem

4. access to the registry key denied

5. To Scott, I dont understand it.

6. Invalid Token

7. VFP 6.0 dont understand type ntext of MSQL 7.0

8. How to view the content of database's log file

9. dont recruit on this news group if you dont have any jobs

10. (dont groan) Index assistance - queuing system

11. codepages dont match?

12. Dynamic filter with join dont work