Cannot cast as datetime
I have a date field that I need to cast to datetime. However when I do isdate on that field a get a few hundred rows that are like 2/31/2009 it's in the format I want just it obviously isn't a real date. I tried creating the code below to subtract 3 days from the date, but I'm still getting the error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Is there a different way to do this?
select case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
else datediff(d,DateOpened, - 3) end as InvoiceDate
from Strat_Customers.dbo.Customer_guardASKER
nope still same error
>>datediff(d,DateOpened, - 3)<<
should be
dateadd(d, -3, DateOpened).
But either way, it will not work because 2/31/2009 is not a valid date. There is no February 31.
should be
dateadd(d, -3, DateOpened).
But either way, it will not work because 2/31/2009 is not a valid date. There is no February 31.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
In the end I need it to be a legit datetime for my stored procedure to work correctly. Of the few hundred that are out of range values I wanted to subtract 3 days off of them, then they will all be within range. I didn't know how to accomplish, but above is what I had tried.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm glad Brandon that you agreed with what I've posted above in comment 24032319
Don't flatter yourself into thinking I copied your answer. It wasn't there when I posted the comment.
>>It wasn't there when I posted the comment.<<
that's weird, because you were able to see Angel's comment. :)
Anyhow, Peace man!
Gallitin,
If the invalid date is with Feb 31 only, then I was thinking maybe you could try something like this:
select case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
else cast(replace(DateOpened, '2/31/', '2/28') as datetime) end as InvoiceDate
from Strat_Customers.dbo.Customer_guard
sorry missed one /
select case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
else cast(replace(DateOpened, '2/31/', '2/28/') as datetime) end as InvoiceDate
from Strat_Customers.dbo.Customer_guardASKER
No it happens to be many dates I just figured out that the customer data every month has 31 days for some reason so most months will have bad data. Working on a function for it right now, but taking some time stumbling thru it.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I don't want to b/c it's a invoice date and each invoice has to have one. The column it eventually inserts into doesn't allow nulls and it shouldn't, so I'm trying to build a function to call in the select that will look and see how many days the month should have and then change the date to the last legit day of that month
It might not be efficient, but you can give it a try to this:
select case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
else
case when left(DateOpened, charindex(DateOpened, '/') - 1) = 2 then cast('2/28' + right(DateOpened, 5)) as datetime)
else cast(replace(DateOpened, '/31/', '/30/') as datetime) end
end as invoicedate
from yourtable
ASKER
Data is stored in this format with no /
20090231
20090231
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think http:#a24032397 needs to be included as it was already covered by ralmada.
SET DATEFORMAT MDY