convert varchar to date format and del junk

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 varchar to date format and del junk

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-17 : 10:12:26

I have a varchar field which should contain dates but unfortunately it is fill with all sorts of imaginary formats which only the one who has typed may understand.

9+03/24/2006
3/16/2006
11:15AM
5-Oct
8/8/2006
9
.
`06/27/2006
12505
1/9/2007
1/12/2007
1/12/2007
1/15/2007

in a data set of 15000 rows most of them are like '1/15/2007'
and 305 roes are like the rest in the sample.

can you sugest ma a simple way to convert posible data to format '1/15/2007' format which i can use in a SELECT...INSERT STATEMENT and convert the rest to NULL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-17 : 10:17:50

You can use IsDate() to find out possible valid dates. Update rest of the rows with NULLs.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-17 : 10:36:57

Thanks Harsh isdate() tells if its a std date format or not by (0/1)
but i need to convert them to one singel format

i tryed to create a temp table with sdate as in source and cdate for converted one

create table #tempx (sdate varchar(50),cdate datetime)
insert into #tempx
select fu235#frl_status_uw_submitted,isdate(fu235#frl_status_uw_submitted)
the output is
sdate cdate
09/25/2006 1900-01-02 00:00:00.000
09/28/2006 1900-01-02 00:00:00.000
9 1900-01-01 00:00:00.000
9-19-06 1900-01-02 00:00:00.000

i want the output to be
sdate cdate
09/25/2006 2006-09-25 00:00:00.000
09/28/2006 2006-09-28 00:00:00.000
9 null
9-19-06 2006-09-19 00:00:00.000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 10:37:37

The drawback with ISDATE() is that

11:15AM is valid.
2006 is valid.

So you have to add a another check. Try this

SELECT CASE WHEN ISDATE(YourColumnNameHere) = 1 AND YourColumnNameHere LIKE '[0-9]%/%[0-9]/[0-9][0-9][0-9][0-9]' AND DATALENGTH(YourColumnNameHere) <= 10 THEN DATEADD(day, DATEDIFF(day, 0, CAST(YourColumnNameHere AS DATETIME)), 0)
ELSE NULL END AS YourColumnNameHere
FROM YourTableNameHere

Peter Larsson
Helsingborg, Sweden

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-17 : 11:23:49

one little clarification on this peter my data containg more than this format
'[0-9]%/%[0-9]/[0-9][0-9][0-9][0-9]'

10-4-06 Lenght(7)
9/28/06 Lenght(7)
10-28-06 Lenght(8)
2/28/2006 Lenght(9) how to handle that

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-17 : 11:25:57

Please convert this data to a NEW column with the CORRECT datatype! Never, ever store dates in "particular format" in a VARCHAR column. If you use the correct datatype, formatting is not an issue, having bad data is not an issue, and now you can sort and filter and use formulas on that column correctly.

Please fix your data permanently, don't just clean it up a little and allow it to become a mess again. The very first rule of databases is: always (no exceptions) use the correct datatypes on your columns!!!

- Jeff

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-01-17 : 11:47:23

my data source for this is varchar and i cannot change it. iam pulling the data to create report in microstrategy, where i have date range condetions. so when i am loding the data i want to dump this varcha containing date to a Date datatype column keeping most of the data intact.

the solution peter gave helpes (total 15568 rows of which 15100 are converted but the rest of the data is
10-4-06 Lenght(7)
9/28/06 Lenght(7)
10-28-06 Lenght(8)
2/28/2006 Lenght(9) like this )

this is samplequery

create table #tempx (sdate varchar(50),cdate datetime)
insert into #tempx
select mycolumn,CASE WHEN ISDATE(mycolumn) = 1
AND mycolumn LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]'
THEN convert(datetime,mycolumn,101)
ELSE NULL END
from mytable

to which i want to add a condetion
mycolumn LIKE '[0-9][0-9]-[0-9][0-9]-[0-9][0-9]' or
mycolumn LIKE '[0-9]%-[0-9]%-[0-9][0-9]'
(iam getting a error "Syntax error converting datetime from character string.")

or just help me in converting
a date of the format 10/16/06

UPDATE #tempx
set cdate=CASE WHEN ISDATE(sdate) = 1
ltrim(rtrim(sdate)) LIKE '[0-9]%/[0-9]%/[0-9][0-9]' --YEAR ID OF 2 CHAR
THEN convert(datetime,ltrim(rtrim(sdate)),1)
ELSE NULL END
(iam getting a error "Syntax error converting datetime from character string.")

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 15:18:54

You could try with only ISDATE(xxxx) = 1 and see what happens.
Often SQL Server is clever enough to transform the dates.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources