Solved

Cannot cast as datetime

Posted on 2009-03-31
19
781 Views
Last Modified: 2012-05-06
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
Comment
Question by:N R
  • 7
  • 6
  • 3
  • +2
19 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 24032292
Insert this line before the select statement and retry -

SET DATEFORMAT MDY
0
 
LVL 11

Author Comment

by:N R
ID: 24032303
nope still same error
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24032319
>>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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24032342
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
 
LVL 11

Author Comment

by:N R
ID: 24032371
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
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 50 total points
ID: 24032397
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
 
LVL 41

Expert Comment

by:ralmada
ID: 24032418
I'm glad Brandon that you agreed with what I've posted above in comment 24032319
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24032474
Don't flatter yourself into thinking I copied your answer.  It wasn't there when I posted the comment.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24032547

>>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 41

Expert Comment

by:ralmada
ID: 24032671

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
 
LVL 41

Expert Comment

by:ralmada
ID: 24032682
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
 
LVL 11

Author Comment

by:N R
ID: 24032700
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
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 50 total points
ID: 24032713
Can you not use a filter in the where clause to ignore invalid dates?

where isdate(DateOpened) = 1
0
 
LVL 11

Author Comment

by:N R
ID: 24032731
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
 
LVL 41

Expert Comment

by:ralmada
ID: 24033253
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
 
LVL 11

Author Comment

by:N R
ID: 24033265
Data is stored in this format with no /

20090231
0
 
LVL 11

Accepted Solution

by:
N R earned 0 total points
ID: 24033403
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
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 350 total points
ID: 24033416
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
 
LVL 39

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now