Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 30th, 2003, 12:20 PM
Registered User
 
Join Date: Oct 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL and ISO date problems

Hi - This is my first posting to this forum,
  I'm having problems on just one particular machine. OS is Win 2k, SQL Server 7 SP3. I'm having problems on this machine when inserting an ISO date into a datetime field. I've built a noddy application to demonstrate the problem. The app is VB6 using ADO to talk via a DSN to SQL Serve 7 DB (SQLSVR32.dll). The third parameter in the SQL statement goes into the datetime field. The SQL statement is:

"INSERT INTO CRELQUERIES VALUES( 5, 'Payments/Repayments - July 2002', '2003/01/23', 'TP', 'Services', '', '', '', '', '', '', 'http://www.clientrelate.cch.co.uk/index.asp?pt=detailsservices&pi=crs000010', 'Amount,,', '1', 0, '1.1', 2)"

This generate the error : DIAG [22008] [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. (242)

This only occurs one one of my machines - all the rest appear to work OK.

By using an ISO format date, I should be bypassing all of the local date translation mechanisms, but it doesn't appear to work.

I've written a testbed to nobble the date in the SQL statement to try multiple formats. These are the results:

Format Result
----------------------
'2003/01/23' Fail
'2003-01-23' Fail
'01/23/2003' Fail
'23 Jan 2003' Success
'23/01/2003' Success

The dates in SQL server are all defaulted to be US English. The regional settings on the machine are set up to be British English (not that these should matter).

Can anyone see where I'm going wrong?

Rgrds
  Callum
 
Old October 31st, 2003, 04:10 AM
Authorized User
 
Join Date: Jun 2003
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi.

It is wise to use the unseparated string format when inserting datetimes. This format should work no matter what dateformat the server or client is set up with.

Try '20030123' and se if that will work...

Gert
 
Old December 13th, 2006, 06:25 AM
Authorized User
 
Join Date: Aug 2004
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How do you use the unseparated string format to insert or update datetimes with both a date and a time part (eg how do you insert 25th Jan 2006 09:27)?

 
Old December 13th, 2006, 06:29 AM
Authorized User
 
Join Date: Aug 2004
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've found out - it's '20060125 09:27'

 
Old December 14th, 2006, 03:16 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would suggest you use the convert function and not just the insert as your posted.

 
Old July 23rd, 2007, 11:56 AM
Registered User
 
Join Date: Jul 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a very similar problem.

Two servers one in VMWARE the other a 'real' server.

They have same OS - Sql Server 2000 sp4 with the same regional and locale settings throughout.

Same code base and same database - files detached and copies attached to the other.

One 'real' accepts dates int format '2007-07-01 19:16:34' the other gives

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


With the same regional and local settings and with a date that cannot be interpreted as being anything other than in 2007 and valid even if the month and day are reversed, how can this error be produced.

The code and database has been deployed to many other servers in the past without a problem.

Any help appreciated.

Regards Kevin.


 
Old August 17th, 2007, 02:14 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=82164







Similar Threads
Thread Thread Starter Forum Replies Last Post
ISO Date Problem chrislepingwell SQL Server 2000 2 May 30th, 2006 12:52 PM
Date problems RobCarter Access VBA 3 November 10th, 2005 10:39 AM
Date problems StevieB SQL Server 2000 6 July 11th, 2005 12:25 PM
content="text/html; charset=iso-8859-1" crmpicco HTML Code Clinic 6 June 17th, 2005 05:09 AM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.