Convert Text Field to Date Format

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert Text Field to Date Format

Author

Topic

HelalM
Starting Member

19 Posts

Posted - 2010-10-07 : 10:47:13

I have a filed "thevalue" that is in text format and can take anything including date and blanks! however, I can only extract date int his field when two other conditions are met (see codes). I need to convert this data (in text format) to a date field. I have tried the following and received "Conversion failed when converting datetime from character string."
Here is the code:
select thevalue, rattributeid, description,
case when attributeid = 'C00311490' and description = 'DECISION DATE' and thevalue <> ''
THEN CONVERT(varchar, CONVERT(datetime, thevalue), 101)
else thevalue
end as 'Dec_Date'

I examined the raw data on thevalue when the two conditions (attributeid = 'C00311490') are met. here is a sample of thevalue:
1/11/2001
3/25/2001
11/3/2006
I also used Cast with no avail. Please help.

Thanks,

HM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-07 : 11:08:45

select thevalue, rattributeid, description,
case when attributeid = 'C00311490' and description = 'DECISION DATE' and thevalue > '' THEN CONVERT(varchar(10), CONVERT(datetime, thevalue, 103), 101)
else thevalue
end as [Dec_Date]
From YourTableNameHere

N 56°04'39.26"
E 12°55'05.63"

HelalM
Starting Member

19 Posts

Posted - 2010-10-07 : 13:04:32

now I get this message:

Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

HM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-07 : 13:44:55

Then you have learned a lesson to always use proper datatypes.
Find out which record that cannot be converted into datetime by using

select * from table1 where isdate(thevalue) = 0

N 56°04'39.26"
E 12°55'05.63"

HelalM
Starting Member

19 Posts

Posted - 2010-10-07 : 16:38:15

Lesson learned, but I am just the reader of our company's DB and don't have much control. Now, if run
Select *
from table1
where isdate(thevalue) = 0

I'll get text such as Yes, No, 09030303, MM/DD/YYYY, Blanks, etc. However, if I run
Select *
from referralattribute
where isdate(thevalue) = 0
and attributeid = 'C00311490'
and (thevalue not like '%MM/DD/YYYY%')
and (thevalue not like '')
Then I get no records all

HM

HelalM
Starting Member

19 Posts

Posted - 2010-10-07 : 17:01:21

Well, I have the isdate=(thevalue)=0 in this code. if I remove only this line, then I have 1000s of records like:

8/6/1935
1/11/2001
3/25/2001
11/3/2006

HM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources