Converting textual dates to actual dates
-
(SQL SERVER 7.0)
I have an "OpeningDate" field in a table that's set up as a varchar, but the data it contains is all dates (3rd party app. I didn't set it up this way). I'm trying to set up a SProc that passes startdate and enddate parameters and finds all rows with OpeningDates between the two parameter dates passed. The code I've got so far is below.
If I try to CAST or CONVERT my OpeningDate column to datetime either in the SELECT statement or in the WHERE clause, I get an error 242: 'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'
This SQL below does produce exactly the result set I want if I run it in Q.Analyzer, but the error gets raised as well, so the data won't go back to the report I'm trying to run off this SProc.
How do I get this to work and return what I'm looking for (error-free)?
**************************************************************
CREATE PROC MySProc @startdate varchar(30)='19010101', @EndDate varchar(30)=''
AS
/*List Matters that were opened between the given dates */
--Set a blank EndDate parameter to today's date.
IF @EndDate =''
SELECT @EndDate=getdate()
SELECT Mattername, CONVERT(datetime,OpeningDate) as FormattedOpeningDate
FROM tm5user.Matter
WHERE CONVERT(datetime,OpeningDate) BETWEEN @startdate AND @EndDate
**************************************************************
Many thanks,
-Ed H.
-
Can you give us an example of what the dates look like in the table?
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
The dates (again, in a varchar column) are all in m/d/yyyy format, like this:
3/10/2004
11/9/2003
2/6/2005
and I still get a result set in QA, with LOOKS right; the converted column looks like 2003-09-02 00:00:00.000.
But that's useless if I keep getting an error.
-ed
-
Run this query. Does it return any rows ?
SELECT Mattername, OpeningDate
FROM tm5user.Matter
WHERE ISDATE(OpeningDate) = 0
-
Basically, no, it doesn't.
This query you gave me:
(SELECT Mattername, OpeningDate
FROM tm5user.Matter
WHERE ISDATE(OpeningDate) = 0)
returns just the records where OpeningDate='' . If I add the criteria AND OpeningDate<>'' , then I get no records.
Getting interesting, isn't it?
-e.h.
-
>>returns just the records where OpeningDate=''
Well, that's the entire source of your error. You're telling SqlServer to convert a column to a date and it doesn't know what to convert '' to. Neither do we.
What are your business rules ? What does '' mean ? Is it NULL ?
You'll need to convert these to some value in your original query
eg
Select Convert(datetime, Case OpeningDate When '' Then NULL Else OpeningDate End) As FormattedOpeningDate
-
Thanks, PW. That looked perfect at first.
I tried your suggestion:
Select Convert(datetime, Case OpeningDate When '' Then NULL Else OpeningDate End) As FormattedOpeningDate
as well as this twist on it:
SELECT Convert(datetime, Case OpeningDate When '' Then '19000101' Else OpeningDate End) As FormattedOpeningDate
and this:
SELECT Convert(datetime, Case OpeningDate When '' Then '19000101' Else CONVERT(datetime,OpeningDate) End) As FormattedOpeningDate
Each case returns visually desirable results in the result set (in QA), but I'm still getting the same error 242 about the out-of-range datetime value. There's nothing else in this stored proc, either, other than this SELECT statement. I can't even Insert these results into a temp table with a datetime column for these dates.
???
-Ed
-
Well ... you're using the same expression in the WHERE clause. Are you remembering to handle it the same way in both parts of the query ?
[Note] If you're going to have multiple different queries hitting this table and needing a true datetime datatype, why not wrap it in a view and code it once ?
-
I've long since dropped the WHERE clause. The SELECT statements in my last post all return the error, without any WHERE criteria included at all.
I tried putting that statement in a view, and SELECTing from that view yields the exact same results, with the error.
-
Eheraux,
Your original problem stated:
I have an "OpeningDate" field in a table that's set up as a varchar, but the data it contains is all dates (3rd party app. I didn't set it up this way). I'm trying to set up a SProc that passes startdate and enddate parameters and finds all rows with OpeningDates between the two parameter dates passed.
This should do the trick for you... I formatted the OpeningDate output in a non-traditional manner (format #107). You can change it to anything you want or need...
CREATE PROCEDURE MySProc
/********************************************************************
This stored procedure accepts a start date and an end date as
optional parameters and finds all of the records in a table called
"tm5user.Matter" where the "OpeningDate" field is a character field
and contains a date between the start date and the end date
parameters.
If the start date is omitted, '01/01/1900' is the default start date.
If the end date is omitted, today is the default end date.
--Jeff Moden
********************************************************************/
--===== Define input parameters
@StartDate DATETIME = '01/01/1900',
@EndDate DATETIME = NULL
AS
--===== Since GETDATE() cannot be used as a default date in passed
-- parameters, convert it now
IF @EndDate IS NULL
SET @EndDate = GETDATE()
--===== List Matters that were opened between the given dates
SELECT Mattername,
CONVERT(VARCHAR(25),CONVERT(DATETIME,OpeningDate),107) AS FormattedOpeningDate
FROM tm5user.Matter
WHERE OpeningDate BETWEEN @StartDate AND @EndDate
--===== Exit the sproc
RETURN
Yes, I relied quite a bit on the instrinsic conversions. You can brute force them, if you'd like, but it's not really necessary here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I believe problem is in format of date in string. DD/MM/YY vs MM/DD/YY. Application and SQL Server are using different formats.
So while it's 1/1/1900 everything is all right. But when it comes to 25/01/2005 date becomes "out of range".
Check with Profiler what application sends to SQL as OpeningDate. Probably it will give you all answers.
_____________
Code for TallyGenerator -
It does not matter if OpeningDate is NULL or '', CONVERT will not produce the error (you will get NULL for NULL date and '1900-01-01' for '')
The error is due to either invalid date e.g. '32/12/2005' or you have mixed dates (dd/mm/yyyy or mm/dd/yyyy).
Try these
select CONVERT(datetime,'12/25/2005')
select CONVERT(datetime,'25/12/2005')
one of them will fail with the error
Far away is close at hand in the images of elsewhere.
Anon. -
You could always put this in your Stored Procedure, once you are sure that you have Day, Month, Year format dates
SET DATEFORMAT DMY
As a Uk Sql User, I fall foul of Date Formats regularly.
All Best
Conway
-
Definitely wrap it in a view, or you'll be doing CASTs for the rest of your life. I assume you're not doing updates on the date field, that could be hazardous to the app that populates the table.
I ran into a similar problem where not only were all the date fields in varchars, they were in nvarchars and were taking twice the space.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply