REMOVE THREAD - THANKS ALL FOR HELP

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

Transact-SQL (2000)

REMOVE THREAD - THANKS ALL FOR HELP

Author

Topic

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 14:00:16

Well, seems I finally need some help after responding to thousands of posters :)

I can't figure out how to do a date compare using a date stored in a legacy DB that decided to store it in a VARCHAR field. I've tried both CAST and CONVERT with no success. The below query I would think would work, but it cannot find the alias idate to compare with. or if I use orignal field just pull all the data and ignores my >=

Here is example what I'm trying to accomplish.

invoice_date = VARCHAR field..

SELECT CONVERT(datetime,invoice_date) as idate
FROM INVOICE
WHERE ISDATE(invoice_date) = 1) AND (idate >= '10/1/2003')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 14:08:15

your probably need to do this;

SELECT invoice_date
FROM INVOICE
WHERE ISDATE(invoice_date) = 1) AND (CONVERT(datetime, invoice_date, 101) >= '10/1/2003')

Go with the flow & have fun! Else fight the flow :)

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 14:29:32

Sounded good but:

Syntax error converting datetime from character string.

And what's weird is ISDATE does pull all recongnizable datetime values returned so it should allow convert or cast. Anyone else?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-16 : 15:18:39

Do you have any NULL dates? What's your NULL setting?

SELECT invoice_date
FROM INVOICE
WHERE ISDATE(ISNULL(invoice_date,0) = 1) AND (CONVERT(datetime, invoice_date, 101) >= '10/1/2003')

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 16:35:26

Yes, invoice_date does allow NULL. I adjust my query as you suggested and now getting a different error message.

Arithmetic overflow error converting expression to data type datetime.

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-08-16 : 16:45:16

I've always done this using a derived table. You can't always be sure whether SQL Server will evaluate the IsDate before the date conversion for each row:


SELECT idate 
FROM (
  SELECT CONVERT(datetime,invoice_date) as idate
  FROM INVOICE
  WHERE ISDATE(invoice_date) = 1) ) as d
WHERE  (idate >= '10/1/2003')

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 17:27:19

Thanks for the replys all and all great suggestions, but things still not working. Any other suggestions. I hate when I feel like I'm back peddling. I have narrowed it down to a bogus value somewhere as I can get a specified single row to query correctly. But would assume ISDATE would do the work to find a valid date values.

I search some other things as trimming the field. I know have a set of results but it's ignoring my comparison >= and date format is not what is is stored.

When I try to apply a ex: CONVERT datetime,invoice_date,101) I get..

Syntax error converting datetime from character string. ARRHH...

One of thoughs days.

SELECT idate
FROM (SELECT RTRIM(LTRIM(CONVERT(datetime,invoice_date))) as idate
FROM INVOICE
WHERE invoice_date <> '' AND ISDATE(ISNULL(invoice_date,0))= 1) as d
WHERE (idate >= '10/1/2003')

Apr 21 1999 12:00AM
Apr 21 1999 12:00AM
Apr 21 1999 12:00AM
Jul 6 1999 12:00AM
Jun 30 1999 12:00AM
Apr 20 1999 12:00AM
Jan 9 2000 12:00AM
Jul 13 2000 12:00AM

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 17:56:26

I've tried to narrow this problem down to experimenting with couiple rows giving the problem. Why would sql fail to convert '6/30/03'

Fails when applying CONVERT(datetime,invoice_date) to allow the previous >= comparison.

Syntax error converting datetime from character string.

SELECT idate,tid
FROM (SELECT id as tid, CONVERT(datetime,invoice_date) as idate FROM INVOICES WHERE ISDATE(invoice_date)= 1) as d
WHERE tid IN (9024)

ROWS DATA TRYING TO CONVERT DATE:

6/30/03 9024
6/30/03 9024

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-16 : 19:29:10

??? Post a CREATE TABLE statement and five or six INSERT statement, then give us the query. We should be able to solve it pretty quick that way.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

robg
Starting Member

8 Posts

Posted - 2004-08-16 : 22:34:54

Thanks in advanced. I've simplified it as much as possible with the same error. You'll see the insert contains two valid date but will give you error trying to convert "Arithmetic Overflow"

SCRIPTS:

BEGIN
CREATE TABLE [dbo].[invoices] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[invoice_date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
)
END

GO

INSERT INTO INVOICES(invoice_date) VALUES('6/30/99')
INSERT INTO INVOICES(invoice_date) VALUES('1/2/2001')

EXAMPLE QUERY CAUSES ERROR:

SELECT invoice_date
FROM invoices
WHERE (ISDATE(invoice_date) = 1) AND (CONVERT(datetime, invoice_date) > '10/1/1998')

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-16 : 22:57:34

It works OK for me.

Running

set dateformat YMD

causes it to fail, so that may be your default setting. You can change it with:

set dateformat MDY

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 07:02:24

You coudl fish for duff ones (or use this as the basis of excluding rows from the conversion)

SELECT *
FROM MyTable
WHERE ISDATE(MyColumn) <> 1

You still need the "SET dateformat XXX" stuff to ensure the conversion is as-you-exect on whjatever sever configuration it gets installed on in the future

Kristen

robg
Starting Member

8 Posts

Posted - 2004-08-17 : 10:48:01

I tried dateformat but didn't work for me. In my 20 year programming, never seen something so strange. There global dateformat I can change?

Running SQL2000.

TSQL:

set dateformat MDY

SELECT invoice_date
FROM invoices
WHERE (ISDATE(invoice_date) = 1) AND (CONVERT(datetime, invoice_date) > '10/1/1998')

Same arith prob. Ken, did you do default install with one you tested on.

quote:
Originally posted by kselvia

It works OK for me.

Running

set dateformat YMD

causes it to fail, so that may be your default setting. You can change it with:

set dateformat MDY

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

robg
Starting Member

8 Posts

Posted - 2004-08-17 : 10:56:48

OK DISCOVER ISSUE FIXED THANKS EVERYONE!

Some how it was remembering or temporary compiling the SELECT statement with my comparison. On a hunch change the > '10/1/1998') to > '10/1/98') and ran it with success. The strange part was when I switched it back to > '10/1/1998') it works as well. Go figure..

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-18 : 19:57:39

Not sure if this could be an issue but on your desktop and server in control panel, regional settings, language, date, make sure the date format is m-d-yyyy. I have seen that cause problems with other apps but not QA, but who knows. (And it looks like you fixed it anyway...)

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources