Conver char to date

[budmor]

Hi,

I am importing (using DTS) dates from an ODBC data source which come across as char (6) in the format A21125 for 25th November, 2002. What is the best way to get convert this data type into a meaningful date in SQL Server 2000? I tried doing a string operation as follows:

select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED,
convert(datetime,'200' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
from FACTORD_DTL

and got the following error mesage:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Without the CONVERT I get 2002-11-23 varchar(10). How do a convert this output to a date?

 

[mwolf00]

Your first substring gives you '200' + '11' for the year....



DATE_SHIPPED = 'A21125'

convert(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[budmor]

Hi Mike,

'200' is concatenated with the substring of the date starting at position 2 for 1 character so it gives '2002' for the year.

Thanks

 

[mwolf00]

Bud,

The code you posted says convert(datetime,'200' + substring(DATE_SHIPPED,3,2)..., which would give you the third and fourth letters from the DATE_SHIPPED string concatenated with '200'. I don't know if that is what is causing your error, but it can't help.... -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[budmor]

Sorry Mike,

You are right - I had actually changed it to 2,1 in Query Analyser - I had just pasted an earlier version of it into my posting. Having re-run it it still gives me the same issue. The query parses fine but then gives the syntax error when I run it.

Thanks for your perseverance!

Mickey

 

[mwolf00]

I would use this syntax to convert text to datetime...
cast('11/26/2002' as datetime)

Using that logic....
cast('200' + substring(DATE_SHIPPED,2,1)+ '/' + substring(DATE_SHIPPED,3,2)+ '/' + substring(DATE_SHIPPED,5,2) as datetime)
-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[mwolf00]

OOPS - I woulf actually put the year at the end (US Standard)

cast(substring(DATE_SHIPPED,3,2)+ '/' + substring(DATE_SHIPPED,5,2) + '/200' + substring(DATE_SHIPPED,2,1) as datetime)
-- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[budmor]

Hi Mike,

I'm still getting the same error:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

when doing the CAST either way. The source data type is char(6). Any ideas?

Thanks,

Mickey

 

[mwolf00]

I'm not sure what the problem is. This code works fine in my query analyser...

create table temp1(
date_shipped char(6)
)

INSERT INTO temp1 VALUES ('A21126')

SELECT cast(substring(DATE_SHIPPED,3,2)+ '/' + substring(DATE_SHIPPED,5,2) + '/200' + substring(DATE_SHIPPED,2,1) as datetime)
FROM temp1



drop table temp1



perhaps you have row that doesn't fit the correct date_shipped format???? -- Just trying to help...
<--- This is a wolf? We need a new icon.......
mikewolf@tst-us.com

 

[MeanGreen]

It sounds like you have some invalid data for your conversion. Try this:

select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED,
convert(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
from FACTORD_DTL
where isdate(convert(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))) = 1

Then do this to find the bad data:
select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED
from FACTORD_DTL
where isdate(convert(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))) = 0

Hope this helps.

 

[MeanGreen]

One minor change. In your isdate function, you don't want to attempt to convert to datetime, or you will get errors. Sorry.:

Try this:

select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED,
convert(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2))
from FACTORD_DTL
where isdate('200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2)) = 1

Then do this to find the bad data:

select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED
from FACTORD_DTL
where isdate(datetime,'200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2)) = 0

Hope this helps.

 

[budmor]

That works where the value of isdate returns as 1, but I get the same error as before when I change the return value to 0, becuase the second bit of code produced an error:

Server: Msg 174, Level 15, State 1, Line 3
The isdate function requires 1 arguments.

It's actually what I need as in some instances DATE_SHIPPED does not have a value so I'm not concerned with those entries!

Thanks a lot everyone for your help.

Regards,

Mickey, Galway, Ireland.

 

[MeanGreen]

This is the query to find those bad entries. I left the datetime parameter in by mistake:

select ITEM_NUMBER,CUSTOMER_ID, PART_NUMBER, DATE_SHIPPED
from FACTORD_DTL
where isdate('200' + substring(DATE_SHIPPED,2,1)+ '-' + substring(DATE_SHIPPED,3,2)+ '-' + substring(DATE_SHIPPED,5,2)) = 0

Hope this helps.