Need to compare a varchar date to getdate

[thorny00]

Have a field - enddate that's a varchar and looks like 08122005. There are also blanks in the enddate field. I need to get only those records where enddate < getdate, I do not want the blanks either in my results. How is this done?

Thanks in advance for your time and help!

 

[gmmastros]

First, I would like to recommend that you change the data type of the field. It would make this query be trivial. However, I do understand that you can't always do this, so, something like this may work.

Notice that I first do a subquery to filter out records where the length of the EndDate field <> 8.

Select	Fields
From	(Select Fields From Table Where Len(EndDate) = 8) A
Where	Convert(DateTime, SubString(A.EndDate, 1, 2) + '/' + SubString(A.EndDate, 3, 2) + '/' + SubString(A.EndDate, 5, 4)) < GetDate()

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

You'll need to convert the enddate field to a datetime field to do your conversion. The Blanks should be converted to 1/1/1900 00:00:00.000.

where convert(datetime, enddate) < getdate()

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[amrita418]

Try this:

SELECT *
FROM   TableA
WHERE  stuff(stuff(field,
                   3,
                   0,
                   '-'),
             6,
             0,
             '-') < getdate()
       AND field > ''

Regards,
AA

 

[thorny00]

Thanks for all of your responses. I keep getting the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Any further suggestions?
Thanks again, I really appreciate it.

 

[gmmastros]

The problem with using a varchar field to store dates is that you can put ANYTHING in to the field. You probably have bad data in your table.

Try this to find the bad data.

Select Max(Convert(Integer, SubString(EndDate, 5, 4))),
Min(Convert(Integer, SubString(EndDate, 5, 4)))
From <tableName>
Where Len(EndDate) = 8

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

You can also try adding and isdate check to make sure only the valid date are passed to the query.
Something like:

SELECT *
FROM   TableA
WHERE  isdate(stuff(stuff(field, 3, 0, '-'), 6, 0, '-')) = 1
       AND stuff(stuff(field, 3, 0, '-'), 6, 0, '-') < getdate()
       --AND field > ''

Regards,
AA