Simple Query won't work - SQL Server Forums

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)

Simple Query won't work

Author

Topic

aawan
Starting Member

24 Posts

Posted - 2007-01-04 : 13:10:05

Hope everyone had a happy holidays and great new year.

I have a simple query which works on my development server, but errors out on my production server.

First of all, the tables are identical, and from what I can see the databases are setup identically on both servers.

Any help is greatly appreciated,
Thanks in advance,
Ali

This is the query that works on my development server:
SELECT Top 1 EffectDate
FROM CommissionsWeekly
ORDER BY CONVERT(datetime, EffectDate) DESC

This is the error I get when I run it on my production server:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The setup for the databases for the collation is:
SQL_Latin1_General_CP1_Cl_AS

Compatibility level for both databases is 80

This is the table script for my dev server:
/****** Object: Table [dbo].[CommissionsWeekly] Script Date: 1/4/2007 10:05:12 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CommissionsWeekly]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CommissionsWeekly]
GO

/****** Object: Table [dbo].[CommissionsWeekly] Script Date: 1/4/2007 10:05:12 AM ******/
CREATE TABLE [dbo].[CommissionsWeekly] (
[FSR] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicyNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Insured] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TR] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryDate] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EffectDate] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetPayable] [float] NULL ,
[DealerID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fileDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CommissionsWeekly] ADD
CONSTRAINT [DF_CommissionsWeekly_fileDate] DEFAULT (getdate()) FOR [fileDate]
GO

This is the table script from my production server
/****** Object: Table [dbo].[CommissionsWeekly] Script Date: 1/4/2007 10:02:52 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CommissionsWeekly]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CommissionsWeekly]
GO

/****** Object: Table [dbo].[CommissionsWeekly] Script Date: 1/4/2007 10:02:52 AM ******/
CREATE TABLE [dbo].[CommissionsWeekly] (
[FSR] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicyNumber] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Insured] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TR] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EntryDate] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EffectDate] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetPayable] [float] NULL ,
[DealerID] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileDate] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CommissionsWeekly] ADD
CONSTRAINT [DF_CommissionsWeekly_FileDate] DEFAULT (getdate()) FOR [FileDate]
GO

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-01-04 : 13:21:43

You have a/some value(s) in EffectDate that can't convert to datetime datatypes. Start looking there. If you need more help post some sample data for EffectDate.

Sorry should have been more specific. Datetime has a limit of 01/01/1753 - 12/31/9999

aawan
Starting Member

24 Posts

Posted - 2007-01-04 : 15:54:32

Joe,

EffectDate is stored as a varchar, because we do a bulk insert from a flat file.

All EffectDates are in the same format 'xx-xx-xx'. Where x is a digit between 0-9.

It's hard to eyeball this because there's > 20k records in the production db.

But from what I have checked, every year is preceded by a '0'. so everything is either 04, 05, 06, or 07. Since these are 2 digit years, I really don't understand how it can get out of range.

That's why I thought it may be some setting in the database that I forgot about.
Thanks for taking the time to help me out...

quote:
Originally posted by JoeNak

You have a/some value(s) in EffectDate that can't convert to datetime datatypes. Start looking there. If you need more help post some sample data for EffectDate.

Sorry should have been more specific. Datetime has a limit of 01/01/1753 - 12/31/9999

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-01-04 : 16:53:29

You can still have out of range errors based on day of month. Assuming that your "xx-xx-xx" format is "mm-dd-yy", you can try something like this...

--Feb..Of course you have to take into account leap years
--which I haven't done here
Select EffectDate
From CommissionsWeekly
Where Left(EffectDate, 2) In ('02')
And Convert(int, SubString(EffectDate, 4, 2)) > 28

Select EffectDate
From CommissionsWeekly
Where Left(EffectDate, 2) In ('04', '06', '09', '11')
And Convert(int, SubString(EffectDate, 4, 2)) > 30

Select EffectDate
From CommissionsWeekly
Where Convert(int, SubString(EffectDate, 4, 2)) > 31

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-01-04 : 16:57:41

You may also want to verify that all the data is the proper length:

Select EffectDate
From CommissionWeekly
Where Len(EffectDate) <> 8

....Or you have a non-existent month (again assuming mm-dd-yy).

Select EffectDate
From CommissionWeekly
Where Convert(int, Left(EffectDate, 2)) > 12

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-04 : 17:42:21

This should find your bad dates:


SELECT
	EffectDate 
FROM
	CommissionsWeekly
where
	isnull(isdate(EffectDate),0) <> 1

CODO ERGO SUM

aawan
Starting Member

24 Posts

Posted - 2007-01-04 : 18:55:37

Michael,

Ah that's it!
I actually wrote a script in ColdFusion that looped through the record set and did its own isDate function to find the bad ones.

Thanks to both of you though, for helping narrow down the problem to the solution.

I love this forum.
cheers,
AA

quote:
Originally posted by Michael Valentine Jones

This should find your bad dates:

SELECT
EffectDate
FROM
CommissionsWeekly
where
isnull(isdate(EffectDate),0) <> 1

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-04 : 19:29:05

Why can't you use proper datetime datatype for column even when bulk inserting?

Peter Larsson
Helsingborg, Sweden

aawan
Starting Member

24 Posts

Posted - 2007-01-05 : 10:28:30

Peso,

A couple of reasons.
One being that we don't want/need to store the time with the date, for that field.
We want to import it as is in the mm-dd-yy format.

Secondly, the way our import stored procedure works, it reads everything in as a varchar to begin with.

AA

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-05 : 11:25:36

You should still always use the correct data types;during the import you should convert the text data to a standard datetime. Even if you are not storing times, you still should use the correct data types for all of your data, without exception, since only then can you be sure that your data is valid, indexes are used properly, and that it can compare and sort the way you want without conversions.

- Jeff

aawan
Starting Member

24 Posts

Posted - 2007-01-05 : 11:56:14

Jeff,

Point taken.
Now I have another question about indexes.
Isn't it true that you cannot do a bulk insert or an Insert-Select into a table with indexes?

I don't recall correctly, but I do believe I ran into such a problem in the past.

Correct me if I'm wrong, and if you have any tips on how to handle such situations, I would be very grateful.

Thanks,
AA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources