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
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
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
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)
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.