[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • Last Modified:

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_guard

Open in new window

0
Nathan Riley
Asked:
Nathan Riley
  • 7
  • 6
  • 3
  • +2
5 Solutions
 
reb73Commented:
Insert this line before the select statement and retry -

SET DATEFORMAT MDY
0
 
Nathan RileyFounder/CTOAuthor Commented:
nope still same error
0
 
ralmadaCommented:
>>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.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem is that the CAST() will be evaluated also when the isdate() returns false.

also, in the ELSE you use datediff() with dateopened as datetime value implicitly converted.

so, please clarify the expression's requested result.

0
 
Nathan RileyFounder/CTOAuthor Commented:
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.
0
 
BrandonGalderisiCommented:
Also, I think instead of:

else datediff(d,DateOpened, - 3) end as InvoiceDate


you want:

else dateadd(d,-3,DateOpened) end as InvoiceDate

Once you get the other stuff working.  But as angel stated, you won't be able to dateadd() anything with '2/31/2009' because that's not a valid date.  You will need to cleanse your data.
0
 
ralmadaCommented:
I'm glad Brandon that you agreed with what I've posted above in comment 24032319
0
 
BrandonGalderisiCommented:
Don't flatter yourself into thinking I copied your answer.  It wasn't there when I posted the comment.
0
 
ralmadaCommented:

>>It wasn't there when I posted the comment.<<
that's weird, because you were able to see Angel's comment. :)

Anyhow, Peace man!
0
 
ralmadaCommented:

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

Open in new window

0
 
ralmadaCommented:
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_guard

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
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.
0
 
reb73Commented:
Can you not use a filter in the where clause to ignore invalid dates?

where isdate(DateOpened) = 1
0
 
Nathan RileyFounder/CTOAuthor Commented:
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
0
 
ralmadaCommented:
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

Open in new window

0
 
Nathan RileyFounder/CTOAuthor Commented:
Data is stored in this format with no /

20090231
0
 
Nathan RileyFounder/CTOAuthor Commented:
Well working now I'll post up the function just in case it will help anyone searching for an answer:
/******************************************************************************
Purpose: 
 
 
Maintenance History:
Date:		Name:			Action:	
-------------------------------------------------------------------------------
03/31/2009	Nathan          	Created
******************************************************************************/
CREATE function dbo.F_FixDate_guard
(
	@Date varchar(255)
)
 
returns datetime
	
as  
begin 
 
set @Date = ltrim(rtrim(@Date))
 
if @Date = '00/00/00' 
	return null
 
declare @WorkingDate varchar(255)
 
set @WorkingDate = @Date
 
if substring(@Date,7,2) > 31 
	set @WorkingDate = substring(@Date,0,5) + substring(@Date,5,2) + '31'
 
if substring(@Date,7,2) > 30 and substring(@Date,5,2) not in (1,3,5,7,8,10,12)
	set @WorkingDate = substring(@Date,0,5) + substring(@Date,5,2) + '30'
 
if substring(@Date,7,2) > 28 and substring(@Date,5,2) not in (1,3,4,5,6,7,8,9,10,11,12)
	set @WorkingDate = substring(@Date,0,5) + substring(@Date,5,2) + '28'
 
return cast(@WorkingDate as datetime)
 
end

Open in new window

0
 
ralmadaCommented:
Ok, last try, dealing with leap year problem as well.
select 	case when isdate(DateOpened) = 1 then cast(DateOpened as datetime)
	else 
		case when substring(DateOpened, 5, 2) = 2 then 
				case   
					when left(DateOpened, 4)%400=0 then cast(convert(varchar(6), left(DateOpened, 6)) + '29' as datetime)
					when left(DateOpened, 4)%100=0 then cast(convert(varchar(6), left(DateOpened, 6)) + '28' as datetime)								when left(DateOpened, 4)%4=0 then cast(convert(varchar(6), left(DateOpened, 6)) + '29' as datetime)
					else cast(convert(varchar(6), left(DateOpened, 6)) + '28' as datetime) 
				end
		else cast(convert(varchar(6), left(DateOpened, 6)) + '30' as datetime) end
	end  as invoicedate
from yourtable

Open in new window

0
 
BrandonGalderisiCommented:
I don't think http:#a24032397 needs to be included as it was already covered by ralmada.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now