convert string to datetime

[simma]

Hello,
I am importing data from flat file to sqlserver.How can I convert a string to datetime.It errors out when I try to import changing the datatype.
Thanks

 

[ClaireHCM]

select convert(datetime,'3/2/2003')

if you are converting the whole table.(table name:mytbale,column name string)

select convert(datetime,string) from mytable

what error you are getting?

 

[simma]

Thanks,
I am getting 'cannot convert string to datetimestamp'

 

[ClaireHCM]

how is the string look like?
Could you provide little more info?

 

[cmgaviao]

since dates have a tendency to sometimes get fat fingered or corrupted in some other way, you might consider wrapping a case statement around the convert so that you don't error out on bad data:

select case when isdate(mytxtdatefield)= 1 then
convert(datetime,mytxtdatefield) else
cast(null as datetime)
end
from mytable

 

[simma]

Thanks very much!!
The string looks like 1172003.I want to convert to datetime 1/17/2003.I need it as datetime bcos I want to compare with otherdate field

 

[simma]

when I use convert function it gives the following error
'Syntax error converting datetime from character string. '
My stetemnt

select convert(datetime,escana_date) as edate
from dbo.SI_SRVDSR

 

[ClaireHCM]

Actually it's very difficult to code your requirement.
Why,
take 1172003,I thought it's 11/7/2003.But actually it's 1/17/2003(only after reading your question detaily I came to know).Both are right.Though we can do some trick to seperate 1172003.But how the coding is goint identify whether it need to do 1/17/2003 or 11/7/2003?

why you are getting erroris because query analyzer dont know which date it should take.

Do a small test

select convert(datetime,'20030113')
and
select convert(datetime,'2003113')

from my point of view both represent the same date.How about QA,it need to take 2003/1/13 or 2003/11/3?
This is the reason you are getting error.If you can specify by adding zero before the days and months which is no more than 10.(instead of 1 input 01).You can help QA to know which date you really want.

Another thing you need to do is exchanging the year position with month and day position.(change 11132003 to 20031113)

 

[simma]

Thanks ClaireHsu